If and vlookup formulas

ekoland

New Member
Joined
Dec 12, 2017
Messages
2
HI all,

Today I had some hard time with this:

I have three sheets: first has id's, second has Id and names and third has some names.

I need to create a flag in the first sheet that calculates if an ID from first sheet is matching with the id from second then match the names from second sheet with names from third. If all the arguments are right then yes if one is false then no.

Is this possible in Excell?

Thanks!
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
Let's say that you have the following structure:

Sheet1
Column A: ID column

Sheet2
Column A: ID column
Column B: Name column

Sheet3
Column A: Name column

Then, for any entry in cell A2 on Sheet1, place this formula in cell B2 on that sheet:
Code:
=IF(ISNA(VLOOKUP(VLOOKUP(A2,Sheet2!A:B,2,0),Sheet3!A:A,1,0)),"No","Yes")

That should do what you want.
 

ekoland

New Member
Joined
Dec 12, 2017
Messages
2
tried is seems that this is not working. Maybe I was not clear enough!

Sheet 1 has lets say 100 ID's from which 50 of it are common with the ID's from Sheet 2.
Sheet 2 has 70 ID's and 70 Names from which only 20 of these names are common with Sheet 3.
Sheet 3 has only 10 names.

My formula needs to verify if an ID from Sheet1 is matching with an ID from Sheet 2, if this argument is true the next step is to link the Names from Sheet2 with Sheet3 and if the argument is true the final result should be YES if one of these arguments is false then the final result should be NO.

Hope this is more clear!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
My formula needs to verify if an ID from Sheet1 is matching with an ID from Sheet 2, if this argument is true the next step is to link the Names from Sheet2 with Sheet3 and if the argument is true the final result should be YES if one of these arguments is false then the final result should be NO.

Hope this is more clear!
Nope. It was entirely clear, and that is exactly what I programmed for.

If it is not working, my guess is one of two things is going on:

1. You have not incorporated the formula correctly. If you want help with that, you need to be more descriptive.
This is not descriptive enough:
Sheet 1 has lets say 100 ID's from which 50 of it are common with the ID's from Sheet 2.
Sheet 2 has 70 ID's and 70 Names from which only 20 of these names are common with Sheet 3.
Sheet 3 has only 10 names.
Exactly which columns does this data reside in on each sheet?
Also, please post your formula exactly as you have it.

2. There are data issues and your data really does NOT match.
Common issues include:
- Comparing Text to Numeric data. That won't work. You can only compare Text-to-Text or Numbers-to-Numbers.
- There are extra spaces in some of your strings. In order to have a match, they MUST match EXACTLY, right down to extra spaces.

I would recommend that you really do, indeed, have a match.
Let's say that you think that cell A4 on Sheet1 matches cell A29 on Sheet2, and cell B29 on Sheet2 matches cell A113 on Sheet3.
Then enter these two formulas and see if they both return TRUE.
Code:
=Sheet1!A4=Sheet2!A29
=Sheet2!B29=Sheet3!A113
So, locate an apparent match and follow this process to see if it really is a match.
If they both return TRUE, and your formula is not working out, then there is an issue with your formula.
If one or more returns FALSE, then you have a data issue, and your apparent matches really do not match. So you need to find out why and either clean up the data, or modify the formula to account for the data discrepancy.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,597
Messages
5,597,097
Members
414,123
Latest member
fxledi

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