Look up with 2 criteria

[Eldexoly]

Hi guys,

I've the impression this is a recurrent topic, but I haven't been able to find a working solution on the internet after several hours of investigation .

I am trying to retrieve the data from one column, based on the values of two other columns (criteria). We can assume there are no repetitions. I am trying to achieve this with a combination of INDEX + MATCH and Worksheetfunction or Evaluate, because:

- other potential solutions include loops, and this may take lot of time in my Excel, which contains several thousands of rows
- I am looking for the simplest solution, and using Excel formulas looks simpler that several nested loops

I have managed to make the code work, but when I try to replace the specific addresses e.g. Source!B3:B9 with variables e.g. Rng_Country is when the code does not work, and (I think) I need to use variables because I want to apply this logic to several columns and thousands of lines.

Could any of you shed some light about the correct way here?

Many thanks

[Pete_UK]

You can use this array* formula in D3:

=INDEX(Source!$D$3:$D$9,MATCH(1,(B3=Source!$B$3:$B$9)*(C3=Source!$C$3:$C$9),0))

*Note that an array formula needs to be confirmed using the key combination of Ctrl-Shift-Enter (CSE) instead of the usual Enter.

Copy the formula down using your usual method(s).

Hope this helps.

Pete

[Eldexoly]

Hi Pete_UK,

Thank you for the suggestion. I was looking for a VBA solution. Finally I felt in the arms of the loop, but I apply it to an array loaded in memory, so I expect it to be faster. Attached the Excel with the solution for future reference.

Have a great eve.