[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Excel Lookup with IF condition?

Posted on 2012-04-12
10
Medium Priority
?
192 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
Comment
Question by:plucenko
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
10 Comments
 
LVL 8

Accepted Solution

by:
Joe Overman earned 2000 total points
ID: 37840137
You can use a VLOOKUP.  see the attached file
ExampleBook.xlsx
0
 
LVL 9

Expert Comment

by:Aeriden
ID: 37840151
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

by:plucenko
ID: 37840157
How do I get rid of n/a#
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 9

Expert Comment

by:Aeriden
ID: 37840180
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

by:plucenko
ID: 37840184
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

by:Aeriden
ID: 37840202
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

by:plucenko
ID: 37840518
Yes
0
 
LVL 9

Expert Comment

by:Aeriden
ID: 37842973
Here is a new version with a single tab.
0
 

Author Comment

by:plucenko
ID: 37842977
Can you attach the file - Thanks!!
0
 
LVL 8

Expert Comment

by:Joe Overman
ID: 37843093
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

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

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

Join & Ask a Question