Solved

Conditional VLookup

Posted on 2013-12-11
9
389 Views
Last Modified: 2013-12-11
How do I built a conditional Vlookup?

For example, if source cell = X, then use =VLOOKUP(F1,A1:B2000,2,FALSE)
... where I look-up values from the 2nd column from the lookup range.

However, if source cell = Y, the use =VLOOKUP(F1,A1:B2000,3,FALSE)
... where I now want to look-up values from the 3rd column

EEH
0
Comment
Question by:ExpExchHelp
  • 4
  • 4
9 Comments
 
LVL 32

Accepted Solution

by:
Rob Henson earned 250 total points
ID: 39711102
=VLOOKUP(F1,A1:B2000,IF(source=x,2,3),FALSE)
0
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 250 total points
ID: 39711103
Depends on the exact situation as to what the best formula will be, but you can use something like this:

=VLOOKUP(F1,A1:B2000,IF(A1="X",2,3),FALSE)

if A1 only has X or Y.
0
 

Author Closing Comment

by:ExpExchHelp
ID: 39711108
Thanks!
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 39711113
Just noticed, the formula as described above will result in an error; A1:B2000 is only 2 columns but you are wanting to return the value from a third column, in amendment below I have changed to C2000 so that we have 3 columns.

You may also be able to use the MATCH function to return the column.

If your source cell (A1 as suggested by rorya) is the same as a column header in the data, you can find the relevant column by matching it in the header row:

=VLOOKUP(F1,A1:C2000,MATCH(A1,A1:C1,0),FALSE)

Thanks
Rob
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:ExpExchHelp
ID: 39711417
Rob:

Thanks for chiming in... I appreciate it.

I earlier looked at the VLookup and it appeared to make sense.   However, I didn't get a chance to actually test it (busy morning).

Yes, you're right, the proposed Vlookup didn't work for me either.

Please find attached XLS.  
- Value in A1 is a drop-down (male, female example)
- Lookup values are in K:M
- Based on the ID number, I need all 3 males OR all 3 females (again, just an example) to be shown in column D2:D5.

Please see attached XLS for illustration purposes.

Thanks for the continued help.

EEH
Book1.xls
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 39711528
In your example, the formula would be:

=VLOOKUP($C2,$J$1:$M$4,IF($A$1="Male",3,4),FALSE)

However, would your data set really look like the example in J1:M4?

Normally, I would expect to see a unique ID for each individual name.

Are you trying to filter your dataset based on a specific criteria, eg in your example Male or Female?

See the attached amended file. With the arrangement of Data on Suggested tab, you can then use simple column Filters or Advanced FIlter function to produce the results expected in C:D columns.

To replicate, the file should remember the Advanced Filter settings I used. I assume you are using excel 2003 going by the .xls file name. The AF wizard is in Data menu > Filter > Advanced Filter.

If version assumption is incorrect, the AF is Data tab of ribbon > Sort & Filter Group > Advanced.

AF asks for:
1) Data area
2) Criteria
3) Destination (greyed out if copy to another location is not ticked and will filter in place).

The criteria is A1:A2, the dropdown is now in A2 because I have added a header the same as the data table in A1 which the AF function needs.

Thanks
Rob H
Copy-of-Book1.xls
0
 

Author Comment

by:ExpExchHelp
ID: 39711579
Rob:

Thanks... your proposed VLookup looks great.    

I agree, this would be a poor representation of data (using the male/female example).

I only used it for illustration purposes as it doesn't require any insight understanding about my process.    So, for the actual data set, I used a totally different value set (numbers only).

Applying your formula will definitely assist me in this particular work.

Again, thanks!!

EEH
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 39711598
No worries, is this related to the Dynamic Drop Down question you also have open?
0
 

Author Comment

by:ExpExchHelp
ID: 39711620
Rob - thanks.  No, this is a different problem.

EEH
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Tags from access to excel 3 32
ADD New Entries 7 16
Avoid Multiple Instances of Named Ranges in Excel 3 22
Excel - Duplicate values in columns 4 16
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…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
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…

864 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now