CODEWITHSUNDEEP
google-sheets
Michael Jetzer
Michael Jetzer

Reputation: 59

Selecting nth value based on set of cells Google Sheets

I have a set of ID's to compare to a SDE, and I would like to pull multiple rows from the SDE using the ID's as a reference. The reason I want to lookup nth instances is because I have multiple columns in which each column will pull a different instance so all the data can be store horizontally instead of vertically. There will be more ID's but the two on there are just for testing purposes.

The current function I have is =ARRAYFORMULA(IF(C4:C="",,INDEX(SDE_materials_mat,SMALL(IF(C4:C=SDE_materials_id,ROW(SDE_materials_mat)),1))))

That function displays the following alarm:

Array arguments to EQ are of different size.

Here is a copy of the sheet:

https://docs.google.com/spreadsheets/d/1uPgFYKjfkcLfBTAcuPL__gDYeCmn1Nwu473CFMepaUk/edit?usp=sharing

enter image description here

Thank you in advance for any help, it's very appreciated!

Upvotes: 1

Views: 412

Answers (1)

player0
player0

Reputation: 1

try:

=ARRAYFORMULA(IFERROR(SPLIT(IFNA(VLOOKUP(C4:C, SPLIT(FLATTEN(QUERY(QUERY(FILTER(
 {SDE_invTypeMaterials!B2:B&"¤"&SDE_invTypeMaterials!C2:C, SDE_invTypeMaterials!A2:A&"×"}, 
 REGEXMATCH(SDE_invTypeMaterials!A2:A&"", TEXTJOIN("|", 1, C4:C)), 
 COUNTIFS(SDE_invTypeMaterials!A2:A, SDE_invTypeMaterials!A2:A, 
 ROW(SDE_invTypeMaterials!A2:A), "<="&ROW(SDE_invTypeMaterials!A2:A))<7), 
 "select max(Col1) where Col1 is not null group by Col1 pivot Col2"),,9^9)), "×"), 2, 0)), "¤ ", 1)))

enter image description here

Upvotes: 1

Related Questions