If and vlookup formulas

[ekoland]

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!

 

[Joe4]

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:

=IF(ISNA(VLOOKUP(VLOOKUP(A2,Sheet2!A:B,2,0),Sheet3!A:A,1,0)),"No","Yes")

That should do what you want.

 

[ekoland]

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]

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.

=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.