Search code examples
excelexcel-formula

XLOOKUP with flexible column and search criteria which is not unique

A B C D E F G H I
1 2023 2024 Column
2 2024
3 Bike A Shop1 500 60
4 Bike B Shop4 100 150 Selected Data Result
5 Bike C Shop3 800 200 150 Bike B
6 Bike D Shop2 90 150 150 Bike D
7 Bike E Shop1 45 790 790 Bike E
8 Bike F Shop1 600 40 980 Bike G
9 Bike G Shop4 900 980

In Range I5:I8 I want to select the corresponding value from Column A based on the number in Range H5:H8 and the column in Cell H2.

Currently, I am trying to use this VLOOKUP function:

=XLOOKUP(H6,$F$1:$F$9,$A$1:$A$9,NA(),0)

However, as you can see

  1. the Column F is fixed but it should be flexible based on the input in Cell H2 and

  2. if a number appears multiple times in Range H5:H8 (in this example 150) the formula only displays the first finding (in this example only Bike B) but it should display all findings.

How do I have to modify it to make it work?

Solution

  • Try using the following formula:

    enter image description here

    =FILTER(A3:A9,1-ISNA(XMATCH(CHOOSECOLS(E3:F9,XMATCH(H2,E1:F1)),H5:H8)))

    Or,

    =FILTER(A3:A9,1-ISNA(XMATCH(XLOOKUP(H2,E1:F1,E3:F9,""),H5:H8)))

    Or,

    =FILTER(A3:A9,COUNTIF(H5:H8,XLOOKUP(H2,E1:F1,E3:F9,"")))