plucenko
asked on
Excel Lookup with IF condition?
I have Client List In ONE worksheet
Client List:
1234
2345
3456
4567
and Super Client in another Worksheet which MIGHT be the same number as a client..
1234
9876
8765
4567
In Workgroup 1 - I need to display Two Colums and MATCH the corresponding Super client..
Client - Super Client
1234 - 1234
How can I achieve this in excel? Via a lookup? Example?
Client List:
1234
2345
3456
4567
and Super Client in another Worksheet which MIGHT be the same number as a client..
1234
9876
8765
4567
In Workgroup 1 - I need to display Two Colums and MATCH the corresponding Super client..
Client - Super Client
1234 - 1234
How can I achieve this in excel? Via a lookup? Example?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
How do I get rid of n/a#
There are a couple of ways to handle this (looking at cell B4 of the demo)...
=IF(ISERROR(VLOOKUP(A4,'Cl ient Lookup'!$A$2:$B$28,2)), "", VLOOKUP(A4,'Client Lookup'!$A$2:$B$28,2))
This will get rid of all #N/A errors. However, you may want the errors if there is indeed no lookup found, but only if something is specified.
=IF(A4="","",VLOOKUP(A4,'C lient Lookup'!$A$2:$B$28,2))
This is probably the preferred method. But thought it would be handy to know both.
=IF(ISERROR(VLOOKUP(A4,'Cl
This will get rid of all #N/A errors. However, you may want the errors if there is indeed no lookup found, but only if something is specified.
=IF(A4="","",VLOOKUP(A4,'C
This is probably the preferred method. But thought it would be handy to know both.
ASKER
What happens if I had the Super Client in one column instead of a sheet? How would i reference that... Thanks.
I guess I am not exactly sure what you mean... Do you mean instead of having two tabs, both are in one tab?
ASKER
Yes
Here is a new version with a single tab.
ASKER
Can you attach the file - Thanks!!
Sorry for not getting back sooner and I am glad Aeriden as provided good answers while I was out.
The way VLOOKUP works is pretty simple. I could spend coming up with a great explanation but MatthewsPatrick has great article on it.
Six Reasons Why Your VLOOKUP or HLOOKUP Formula Does Not Work
Also attached is an answer to you last question. If you award points I would give them to Aeriden.
ExampleBook.xlsx
The way VLOOKUP works is pretty simple. I could spend coming up with a great explanation but MatthewsPatrick has great article on it.
Six Reasons Why Your VLOOKUP or HLOOKUP Formula Does Not Work
Also attached is an answer to you last question. If you award points I would give them to Aeriden.
ExampleBook.xlsx
Client-Lookup.xlsx