vlookup or index - exclude few items in data range

aravindhan_31

Well-known Member
Joined
Apr 11, 2006
Messages
672
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

easy2understandexcel

Active Member
Joined
Dec 26, 2012
Messages
299
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

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,603
Messages
5,832,670
Members
430,153
Latest member
Javid_P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top