vlookup or index - exclude few items in data range

[aravindhan_31]

Hi,

I have the data sheet 1 like country in column A, emp name in col B & Salary in Col C

In sheet 2, I have few names, in Col A like John, Peter Jack etc. I have to do a lookup and bring their salary in col B
I use simple lookup = vlookup(A2, Sheet1B:C,2,0) this works without any problem,
the challenge now is, I want to do this by excluding some countries,
eg. in sheet 1, under india & China I have the name John, when I do a lookup it gives me the first result. How do I exclude the countries in my lookup formula itself? if I say exclude india in the formula, then vlookup up result will be from china even though John name is there in India. if I exclude both china & India in my formula then no result should come.

Can someone help me with the formula or macro for this?

Regards
Arvind

 

[easy2understandexcel]

A very simple "hack" if you are looking for the exact country would be to make a hidden column that combines the name and the country... =A1&B1 for example into cell X1.
Then you can search for the name and country: =vlookup(X1, Sheet1!D:E,2,0)

 

[FDibbins]

I would suggest something similar, except I would have a list of countries to exclude, and use the helper column to pull in only the countries that do not appear on that list with a formula like...
=if(iserror(match(B2,list-of-counties,0)),"",B2)
where List-of-counties is your list and B2 contains the country name in your data base. Then just use that helper, instead of the actual list. If you are using VLOOKUP, you will need to add the helper to the LEFT of your current country column, otherwise you could use INDEX/MATCH