+ Reply to Thread
Results 1 to 18 of 18

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

  1. #1
    Forum Contributor
    Join Date
    02-16-2012
    Location
    Denmark
    MS-Off Ver
    O365
    Posts
    255

    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

    Thanks in advance
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    61,996

    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)))
    Attached Files Attached Files
    Last edited by AliGW; 04-27-2022 at 02:30 AM. Reason: Workbook attached.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  3. #3
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,766

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

    Quote Originally Posted by AliGW View Post
    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. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    61,996

    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. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    61,996

    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.
    Attached Files Attached Files

  6. #6
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,038

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

    Please try

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

  7. #7
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,766

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

    Quote Originally Posted by AliGW View Post
    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. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    61,996

    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. #9
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,766

    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
    Last edited by AliGW; 04-27-2022 at 02:55 AM. Reason: PLEASE don't quote unnecessarily!

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    61,996

    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. #11
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,766

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

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

  12. #12
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,038

    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
    Attached Files Attached Files

  13. #13
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,766

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

    Quote Originally Posted by Bo_Ry View Post
    2010 formula
    Wow nice

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

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    61,996

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

    @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.

  15. #15
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,038

    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. #16
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    61,996

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

    @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.

  17. #17
    Forum Contributor
    Join Date
    02-16-2012
    Location
    Denmark
    MS-Off Ver
    O365
    Posts
    255

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

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

  18. #18
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    61,996

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

    Glad to have helped - thanks for the feedback.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Xlookup when both the lookup value and lookup array are separated by commas
    By Eades1412 in forum Excel Formulas & Functions
    Replies: 19
    Last Post: 02-08-2022, 04:49 AM
  2. XLookup array formula will not fill down
    By botoole54 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-07-2022, 04:40 AM
  3. Replies: 7
    Last Post: 12-10-2021, 02:17 PM
  4. [SOLVED] Xlookup several columns with multiple lookup values
    By Eades1412 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-14-2021, 10:22 PM
  5. Replies: 7
    Last Post: 07-16-2021, 11:04 AM
  6. Multiple criteria for XLOOKUP formula
    By mikehk in forum Excel General
    Replies: 1
    Last Post: 06-28-2021, 05:50 PM
  7. Replies: 4
    Last Post: 05-19-2015, 08:42 PM

Bookmarks

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