EQUIV INDEX Multi-criteria & multi-tabs
Solved
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
-
ContributorHello
your link is not working, but indeed you need to name the sheet both times.
And check with the attached file that you are entering the formula in array form
see the site where you will find this example that seems to work:
https://mon-partage.fr/f/MGNWnnBf/
and if there is a problem, use the same method to upload yours and come back to paste the link
best regards
--
The quality of the response mainly depends on the clarity of the question, thank you!-
Thank you again for your response.
It works now :) Unfortunately, I can't use any other transfer site since I'm at work right now...
Final version of the formula:
{=INDEX(VueFAISC!$H$1:$H$52;EQUIV(A2&B2;VueFAISC!$A$1:$A$52&VueFAISC!$C$1:$C$52;0))}
In my first post, I wasn't using "Ctrl+Shift+Enter" correctly, and in my second post, I was using it correctly but had removed the sheet name for the second part of the matrix.
Thank you very much for your help.
Post resolved! ;)
-
