Xlookup or what formula? (with multiple column on lookup array)

Try this:

=INDEX($A$2:$A$4,SUMPRODUCT(($B$2:$K$4=D9)*(ROW($A$2:$A$4)-1)))

Originally Posted by AliGW

Try this:

Me too come up with the same solution, but the problem is that it fails when there is duplicates.

Ex. Cherries is D9

Fine, however the OP has not mentioned what should happen if there are duplicates. I only cater for what they have asked for. I don't try to second guess what they haven't considered yet.

If duplicates are on the same row, this will work:

=INDEX($A$2:$A$4,SUMPRODUCT(($B$2:$K$4=D9)*((ROW($A$2:$A$4)-1))/COUNTIF($B$2:$K$4,D9)))

If there are dulicates on multiple rows, the OP will have to tell us what they want.

Please try

=FILTER(A2:A4,MMULT(--(B2:K4=D9),SEQUENCE(COLUMNS(B2:K4))))

Originally Posted by AliGW

I only cater for what they have asked for.

Agreed. The problem is that the formula throws error when the search item is "Cherries".

Just curious to know the solution. Nothing else

I've told you what I would do about it in post #5.

Posted Post #7 without refreshing the page. So not aware of post #5.

Nicely handled the duplicate issue which helped me in recollecting the method

Thanks. However, as always, Bo_Ry has trumped me with a much slicker approach.

Mine is 2010 so unable to try & learn Bo_Ry's approach

2010 formula

B10
=IFERROR(INDEX($A$2:$A$4,SMALL(IF(MMULT(--($B$2:$K$4=$D$9),TRANSPOSE(COLUMN($B$2:$K$4))),ROW($A$2:$A$4)),ROWS(B$10:B10))-ROW($A$1)),"")

Ctrl+Shift+Enter

Originally Posted by Bo_Ry

2010 formula

Wow nice

I expected that you will come with Aggregate() to avoid CTRL+Shift+Enter

@Elainefish

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

TRANSPOSE , IF requires Ctrl+Shift+Enter

Replace TRANSPOSE(COLUMN($B$2:$K$4)) => ROW(Z$1:INDEX(Z:Z,COLUMNS($B$2:$K$4)))

Replace SMALL(IF with AGGREGATE

=IFERROR(INDEX($A$2:$A$4,AGGREGATE(15,6,ROW($A$2:$A$4)/(MMULT(--($B$2:$K$4=$D$9),ROW(Z$1:INDEX(Z:Z,COLUMNS($B$2:$K$4))))>0),ROWS(B$10:B10))-ROW($A$1)),"")

@Elainefish

You have gone very quiet on us ... Any questions?

If not, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

Glad to have helped - thanks for the feedback.