Hi,
Trying to look up values from one dataset to another.
Regular lookup functions only return first matching cell, however matching cell is repeated
EG. only returns first value, but in first instance colour coded yellow 3 entries are present.
=IFERROR(LOOKUP(2^15,SEARCH(H4,$A$4:$A$12),$B$4:$B$12),"")
I need a look up that will return both location and quantity. Have tried playing around with INDEX functions but this returns wrong values in quantity column
=IF(ISERROR(INDEX($A$4:$B$12,SMALL(IF($A$4:$A$12=$H$4,ROW($B$4:$C$12)),ROW(1:1)),2)),"", INDEX($A$4:$B$12,SMALL(IF($A$4:$A$12=$H$4,ROW($B$4:$C$12)),ROW(1:1)),2))
Could anyone suggest anything else?
Feel free to play around with example spreadsheet
Thanks
Bookmarks