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

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

Hi,

I have tried using xlookup formula and it works only if I have single column for the lookup array
My table would include multiple columns instead of one.
I do not know what formula to use instead of the xlooup for this scenario  Register To Reply

2. ## Re: 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)))  Register To Reply

3. ## Re: Xlookup or what formula? (with multiple column on lookup array) 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  Register To Reply

4. ## Re: Xlookup or what formula? (with multiple column on lookup array)

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.  Register To Reply

5. ## Re: Xlookup or what formula? (with multiple column on lookup array)

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.  Register To Reply

6. ## Re: Xlookup or what formula? (with multiple column on lookup array)

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

7. ## Re: Xlookup or what formula? (with multiple column on lookup array) 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   Register To Reply

8. ## Re: Xlookup or what formula? (with multiple column on lookup array)

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

9. ## Re: Xlookup or what formula? (with multiple column on lookup array)

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   Register To Reply

10. ## Re: Xlookup or what formula? (with multiple column on lookup array)

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

11. ## Re: Xlookup or what formula? (with multiple column on lookup array)

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

12. ## Re: Xlookup or what formula? (with multiple column on lookup array)

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  Register To Reply

13. ## Re: Xlookup or what formula? (with multiple column on lookup array) Originally Posted by Bo_Ry 2010 formula
Wow nice I expected that you will come with Aggregate() to avoid CTRL+Shift+Enter   Register To Reply

14. ## Re: Xlookup or what formula? (with multiple column on lookup array)

@Elainefish

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.  Register To Reply

15. ## Re: Xlookup or what formula? (with multiple column on lookup array)

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)),"")  Register To Reply

16. ## Re: Xlookup or what formula? (with multiple column on lookup array)

@Elainefish

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

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.  Register To Reply

17. ## Re: Xlookup or what formula? (with multiple column on lookup array)

You are all great! This group are always helpful as ever   Register To Reply

18. ## Re: Xlookup or what formula? (with multiple column on lookup array)

Glad to have helped - thanks for the feedback.   Register To Reply