Conditional Vlookup

**apolo1376** · 11-15-2009, 09:27 PM

Hi,

I am trying to use conditional vlookup function in excel. I spend whole day on Friday to make this work. I would appreciate if any one of you can help me.

I get the data in my data sheet. Let me setup the work sheet.

Level1(col A) Level2(col B) Level3(col C) value(col D)
Duration 0.77
Duration Intended 0.52
Duration Unintended 0.25
Country 0.53
Country Intended 0.32
Country Unintended Eur 0.21

Given this, I want to get Country - Intended value 0.32. That is I want to check if Level1 = Country and then Level2 = Intended then get the value 0.32. I want to use vlookups (may be some other function which is stable, i mean it should work all the time not for this set of data i get different data each month). I really appreciate your help.

Thanks
Apollo

**teylyn** · 11-15-2009, 09:44 PM

Apolo, could you possibly put that data in a workbook and attach that? The forum software takes out spaces and your layout no longer shows which columns the data is in .

You can upload a file by clicking "Go Advanced" below and then the paper clip icon.

**apolo1376** · 11-15-2009, 09:59 PM

Sure, What i want is able to select Country-intended and value corresponding to it. Also if i want to select country intended and its value.

Thanks,
Apollo

**teylyn** · 11-15-2009, 10:21 PM

Hi,

there are several ways to skin this cat. To keep life simple, it will be easiest to create a helper column that will be used for the lookup reference

option A: helper column in the table
create a new column after B, and in this new column C enter in C2 and copy down

=A2&B2

Then you can use a VLOOKUP like

=VLOOKUP(A13&B13,C2:E7,3,FALSE)

where A13 and B13 are the cells where you choose your criteria.

You can hide the helper column, if you want.

If you don't like the helper column inside the table for some reason, you can use

Option B: helper column somewhere else on the sheet
somewhere, anywhere on the sheet, let's say in A27, put this formula:

=A2&B2

copy down as many rows as your data has, so you have all the combinations of level 1 and 2.

Now you can use a formula like

=INDEX(A2:D7,MATCH(A13&B13,A27:A32,0),4)

where A13 and B13 are the cells where you choose your criteria

The attached file illustrates both solutions . Each solution has its own data table, one with and one without the helper column.

**apolo1376** · 11-15-2009, 10:33 PM

Thank you, I thought of this solution, my coworker told me there is way of doing using conditional or nested vlookups. I tried whole Friday at work trying to figure this one out. I am wondering if any one of you guys know the solution. If not I will use one you mentioned above.

Thanks again
Apollo

**teylyn** · 11-15-2009, 10:37 PM

Ok,

if your level 1 and level 2 combinations are unique, you could do

=SUMPRODUCT((A2:A7="country")*(B2:B7="intended")*D2:D7)

or with an array formula

=INDEX(A2:D7,MATCH(1,(A2:A7="country")*(B2:B7="intended"),0),4)

confirmed with Ctrl-Shift-Enter