Solved

# Excel Lookup with IF condition?

Posted on 2012-04-12
161 Views
Last Modified: 2012-05-07
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?
0
Question by:plucenko
• 4
• 4
• 2
10 Comments

LVL 8

Accepted Solution

masterjojobinks earned 500 total points
You can use a VLOOKUP.  see the attached file
ExampleBook.xlsx
0

LVL 9

Expert Comment

vlookup() works well for this kind of thing.  I have provided an example spreadsheet.  The first tab has the lookup data.  The setup tab uses the information and does the lookup.
Client-Lookup.xlsx
0

Author Comment

How do I get rid of n/a#
0

LVL 9

Expert Comment

There are a couple of ways to handle this (looking at cell B4 of the demo)...

=IF(ISERROR(VLOOKUP(A4,'Client 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,'Client Lookup'!\$A\$2:\$B\$28,2))

This is probably the preferred method.  But thought it would be handy to know both.
0

Author Comment

What happens if I had the Super Client in one column instead of a sheet? How would i reference that... Thanks.
0

LVL 9

Expert Comment

I guess I am not exactly sure what you mean...  Do you mean instead of having two tabs, both are in one tab?
0

Author Comment

Yes
0

LVL 9

Expert Comment

Here is a new version with a single tab.
0

Author Comment

Can you attach the file - Thanks!!
0

LVL 8

Expert Comment

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
0

## Join & Write a Comment Already a member? Login.

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

#### 772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

#### Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!