asked on 5/17/2011

vlookup

Hi,

I want to implement the following in excel:
I have to sheets A and B. I want to do a lookup a date in sheet A in sheet B and then retrieve the ID of the row. There can be multiple rows for the same date. Then with these multiple rows I have to select a single row based on some ID. Basically it is Vlookup with 2 keys. How to do this?

Thanks

Shilpi

Microsoft Excel

Last Comment

shilpi2

5/17/2011

jppinto

5/17/2011

You can use a formula like this:

=LOOKUP(2,1/((Sheet2!A1:A12=Sheet1!A2)*(Sheet2!B1:B12=Sheet1!B2)),Sheet2!C1:C12)

Please check the attached working sheet.

jppinto
LOOKUP-Multiple-Criterias.xlsx

Saqib Husain

5/17/2011

Can you upload a small excel sample (possibly with fictitious data) and show what you want to achieve?

nike_golf

5/17/2011

Use a sumproduct() formula instead...

Do you have an example of the data?

NG,

ASKER CERTIFIED SOLUTION

jppinto

5/17/2011

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

shilpi2

5/17/2011

ASKER

Hi Jppinto,

Thanks for the prompt reply .
I do not understand what is 2 in the lookup argument? also, what is 1/ in the function.

Thanks

Shilpi

jppinto

5/17/2011

Capture.JPG

shilpi2

5/17/2011

ASKER

Hi Jppinto,

Thanks for the prompt reply .
I do not understand what is 2 in the lookup argument? also, what is 1/ in the function.

Thanks

Shilpi

shilpi2

5/17/2011

ASKER

Hi,

can u please tell me how to use one value to obtain multiple rows corresponding to that value?

Thanks

Shilpi

shilpi2

5/17/2011

ASKER

That is I filter based on value A and obtain multiple rows or the row indices having that value. Then based on value B I select a single row out of the multiple rows.

Thanks

Shilpi

jppinto

5/17/2011

I don't see the logics of that! If you're going to use two criterias, why use on criteria and then apply the second criteria to the firsts results?!?

shilpi2

5/17/2011

ASKER

well because I do not have a column for the 2nd criteria . I just know that the 2nd criterion exists but that is not written in a column

shilpi2

5/17/2011

ASKER

Please see the file
I want to start from sheet1. In sheet1 I have a date. I want to lookup that date in sheet 2. This will return me multiple rows. But I want to select that row for which P is UI. Please note that I do not have P column in sheet1 as I have in sheet2. I just know the code in sheet1.
Book1.xls