# 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

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)))

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

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.

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.

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

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

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

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.

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

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.

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

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

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

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

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.

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)),"")

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.

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

You are all great! This group are always helpful as ever

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

Glad to have helped - thanks for the feedback.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1