# 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
jppinto

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

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

Do you have an example of the data?

NG,
jppinto

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.
shilpi2

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

shilpi2

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

Hi,

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

Thanks

Shilpi
shilpi2

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

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

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

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
Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts

TRUSTED BY