JeffGrant
Well-known Member
- Joined
- Apr 7, 2021
- Messages
- 519
- Office Version
- 365
- Platform
- Windows
Hi All,
Sorry I had to paste this as a table, for some reason the XL2bb addin is not working on my computer under a newly installed windows 11.
But anyway...
Legend: ES to EZ are columns headers, 41 to 52 in column ET are row numbers from the original sheet
I have three ways below to use a multi criteria look in an array and for seom reason the Index/Match wont work.
in ES42, there is =MATCH(ER42,EU41:EZ41,0) which returns column 3 - Ok
in ES43, there is =MATCH(ER43,EU42:EU54,0) which returns row 8. - Ok
in ER44, there is =INDEX(EU42:EZ54,ES43,ES42), which returns 18%. This is using the Match Results cell location instead of the matched results - Ok
in ER48, there is =XLOOKUP(ER42,EV41:EZ41,XLOOKUP(ER43,EU42:EU58,EV42:EZ58),"Error",0), which returns 18% - Ok
Now, in ER46, there is =INDEX(EV42:EZ58,MATCH(1,(ER43=EU42:EU58)*(ER42=EU41:EZ41),0)), which is using the multi criteria Match syntax and it does not work.
I am little perplexed, any ideas & thanks for helping me understand.
Sorry I had to paste this as a table, for some reason the XL2bb addin is not working on my computer under a newly installed windows 11.
But anyway...
Legend: ES to EZ are columns headers, 41 to 52 in column ET are row numbers from the original sheet
I have three ways below to use a multi criteria look in an array and for seom reason the Index/Match wont work.
in ES42, there is =MATCH(ER42,EU41:EZ41,0) which returns column 3 - Ok
in ES43, there is =MATCH(ER43,EU42:EU54,0) which returns row 8. - Ok
in ER44, there is =INDEX(EU42:EZ54,ES43,ES42), which returns 18%. This is using the Match Results cell location instead of the matched results - Ok
in ER48, there is =XLOOKUP(ER42,EV41:EZ41,XLOOKUP(ER43,EU42:EU58,EV42:EZ58),"Error",0), which returns 18% - Ok
Now, in ER46, there is =INDEX(EV42:EZ58,MATCH(1,(ER43=EU42:EU58)*(ER42=EU41:EZ41),0)), which is using the multi criteria Match syntax and it does not work.
I am little perplexed, any ideas & thanks for helping me understand.
Column | |||||||||
ER | ES | ET | EU | EV | EW | EX | EY | EZ | |
v16 | 55% | 41 | 1 | 2 | 3 | 4 | 5 | ||
O2 value | 2 | 3 | 42 | 4 | 25% | 0% | 0% | 0% | 0% |
M2 Value | 11 | 8 | 43 | 5 | 20% | 0% | 0% | 0% | 0% |
results | 18% | 44 | 6 | 17% | 0% | 0% | 0% | 0% | |
45 | 7 | 14% | 0% | 0% | 0% | 0% | |||
index/match | #N/A | 46 | 8 | 0% | 25% | 0% | 0% | 0% | |
47 | 9 | 0% | 22% | 0% | 0% | 0% | |||
double xlookup | 18% | 48 | 10 | 0% | 20% | 0% | 0% | 0% | |
49 | 11 | 0% | 18% | 0% | 0% | 0% | |||
50 | 12 | 0% | 17% | 25% | 0% | 0% | |||
51 | 13 | 0% | 15% | 23% | 0% | 0% | |||
52 | 14 | 0% | 14% | 21% | 0% | 0% | |||
53 | 15 | 0% | 13% | 20% | 0% | 0% | |||
54 | 16 | 0% | 13% | 19% | 25% | 0% | |||
55 | 17 | 0% | 12% | 18% | 24% | 0% | |||
56 | 18 | 0% | 11% | 17% | 22% | 0% | |||
57 | 19 | 0% | 11% | 16% | 21% | 0% | |||
58 | 20 | 0% | 10% | 15% | 20% | 25% |