EQUIV INDEX Multi-criteria & multi-tabs
Solved
Damdam
-
Damdam -
Damdam -
Bonjour,
I would like to perform a VLOOKUP with multiple criteria. I saw on several forums that I could use the combination of INDEX and MATCH. The goal is to combine the two criteria into one.
- Result located in column C
- Value in cells D1 and E1
- Double criteria in columns A and B
- We concatenate the criteria by doing A:A&B:B
=INDEX(C:C;MATCH(D1&E1;A:A&B:B;0))
It works very well when everything is on the same sheet.
However, my source columns are on another sheet (Columns A, B, and C), resulting in my formula:
=INDEX(sheet2!C:C;MATCH(D1&E1;sheet2!A:A&sheet2!B:B;0))
And that’s where it fails. I get a #N/A error. By troubleshooting step by step, I realize that concatenation of two columns using the '&' symbol, specifically in the MATCH function, is not working. The concatenation of these two columns outside the MATCH function works fine.
Do you have any tips to make this work?
Thank you for your help :)
Configuration: Windows / Chrome 78.0.3904.97
I would like to perform a VLOOKUP with multiple criteria. I saw on several forums that I could use the combination of INDEX and MATCH. The goal is to combine the two criteria into one.
- Result located in column C
- Value in cells D1 and E1
- Double criteria in columns A and B
- We concatenate the criteria by doing A:A&B:B
=INDEX(C:C;MATCH(D1&E1;A:A&B:B;0))
It works very well when everything is on the same sheet.
However, my source columns are on another sheet (Columns A, B, and C), resulting in my formula:
=INDEX(sheet2!C:C;MATCH(D1&E1;sheet2!A:A&sheet2!B:B;0))
And that’s where it fails. I get a #N/A error. By troubleshooting step by step, I realize that concatenation of two columns using the '&' symbol, specifically in the MATCH function, is not working. The concatenation of these two columns outside the MATCH function works fine.
Do you have any tips to make this work?
Thank you for your help :)
Configuration: Windows / Chrome 78.0.3904.97
2 answers
-
Good evening:
write and enter as an array formula of course:
=INDEX(sheet2!C:C;MATCH(D1&E1;sheet2!A:A&sheet2!B:B;0))
and since array formulas are resource-intensive, avoid writing AA or BB, but limit your ranges to the number of useful rows to reduce reaction times!
kind regards
-
Thank you for your response.
I tried but unfortunately, it seems that the formula continues to take the second part of the matrix from the initial sheet.
I’ve provided a table as an example, with the formula:
=INDEX(Sheet2!$C$1:$C$5;MATCH(A2&B2;Sheet2!$A$1:$A$5&$B$1:$B$5;0))
The underlined part seems to be taken from Sheet1 and not from Sheet2. This results in the created matrix taking the value from column A of Sheet2 and the value from column B of Sheet1...
And so in my column E of Sheet1, it finds a result for entries 1X and 2X because the entries B1:B5 of Sheet1 are always "X". Whereas they should take the entries B1:B5 from Sheet2 which vary.
https://wetransfer.com/downloads/bc9a385ed70e2106433a723394a0e66d20191121092650/0b66e77712b675d95e26c8da4efc290220191121092650/40881f
Thank you for your help.
Best regards,
Damdam
-
