CODEWITHSUNDEEP
google-sheets
aldi nugraha
aldi nugraha

Reputation: 115

How to do Lookup with multiple row

I want to make some kind of Lookup with this kind of table.

enter image description here

Is it possible or any other way to do this?

Upvotes: 1

Views: 546

Answers (3)

Erik Tyler
Erik Tyler

Reputation: 9355

You do not show your Row numbers or Column letters in your post. But supposing that your search number is in cell F2 and that everything else is arranged relative to that, you can use this rather simple formula in G2:

=ArrayFormula(IFERROR(VLOOKUP(F2,{FLATTEN(B2:C3),FLATTEN(B6:C7)},2,FALSE)))

FLATTEN turns a 2D array into a one-column array, working left-to-right and top-to-bottom, which is perfect for a situation like yours.

It is unclear whether you will only be wanting to search that one number and return one result, or whether you will have several numbers in the search column and wish to get their several results. If the latter, the above formula can easily be modified to handle multiple numbers:

=ArrayFormula(IF(F2:F="",,IFERROR(VLOOKUP(F2:F,{FLATTEN(B2:C3),FLATTEN(B6:C7)},2,FALSE))))

See my comment on your original post as well.

References

Upvotes: 2

Shep
Shep

Reputation: 628

A simple approach is to use the lookup search_result_array parameter, the trick is to work backwards from highest value columns to smallest due to the search_key not found default behavior of reverting to lower values.

=ifna(lookup(E2,$C$2:$C$3,$C$6:$C$7),lookup(E2,$B$2:$B$3,$B$6:$B$7))

enter image description here

https://blog.sheetgo.com/google-sheets-formulas/lookup-formula-google-sheets/

Upvotes: 1

Harun24hr
Harun24hr

Reputation: 37125

You can try below formula-

=INDEX(B6:C7, SUMPRODUCT(ROW(B2:C3)*(B2:C3=F2))-ROW(B1),SUMPRODUCT(COLUMN(B2:C3)*(B2:C3=F2))-column(A2))

enter image description here

Upvotes: 1

Related Questions