vlookup or index - exclude few items in data range

aravindhan_31

Well-known Member
Joined
Apr 11, 2006
Messages
655
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
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,575
Messages
5,596,961
Members
414,114
Latest member
Lost_User21

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