Link to home
Create AccountLog in
Avatar of shilpi2
shilpi2

asked on

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
Avatar of jppinto
jppinto
Flag of Portugal image
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
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image
Can you upload a small excel sample (possibly with fictitious data) and show what you want to achieve?
Avatar of nike_golf
nike_golf
Flag of Afghanistan image
Use a sumproduct() formula instead...

Do you have an example of the data?

NG,
ASKER CERTIFIED SOLUTION
Avatar of jppinto
jppinto
Flag of Portugal image
Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Avatar of shilpi2
shilpi2

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
Avatar of shilpi2
shilpi2

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
Avatar of shilpi2
shilpi2

ASKER

Hi,

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

Thanks

Shilpi
Avatar of shilpi2
shilpi2

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
Avatar of jppinto
jppinto
Flag of Portugal image
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?!?
Avatar of shilpi2
shilpi2

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
Avatar of shilpi2
shilpi2

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