Forum Discussion

rservice0320's avatar
rservice0320
Brass Contributor
Dec 05, 2023

index match not giving correct result

I have tried various reiterations of formulas & either I get the incorrect result or a N/A. Any help would be greatly appreciated.  I have attached the file.  
Formulas and Functions
Patrick2788's avatar
Patrick2788
Silver Contributor
Dec 05, 2023

rservice0320 

Based on the arrangement of your data and the extra argument added to your INDEX, I'm guessing you were trying to use the 'reference' arrangement of INDEX.  It's not commonly used but a solution would look like this (note the parenthesis enclosing the 3 ranges in the first argument):

 

=INDEX(
    ($D$10:$I$11, $D$14:$I$15, $D$18:$I$19),
    XMATCH($A5, $C$10:$C$11),
    XMATCH(D$4, $D$9:$I$9),
    XMATCH($B5, {"ABC", "DEF", "GHI"})
)

 

Resources