vlookup

shilpi2
shilpi2 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
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
Can you upload a small excel sample (possibly with fictitious data) and show what you want to achieve?
Use a sumproduct() formula instead...

Do you have an example of the data?

NG,
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

Commented:
nike_golf: SUMPRODUCT function will not work in these case bacause if may be returning a string instead of a number. If the return value is a number, then you could use a SUMPRODUCT function like this:

=SUMPRODUCT((Sheet2!A2:A12=Sheet1!A2)*(Sheet2!B2:B12=Sheet1!B2)*(Sheet2!C2:C12))

(based om my posted file!)

Author

Commented:
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

Author

Commented:
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

Author

Commented:
Hi,

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

Thanks

Shilpi

Author

Commented:
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

Commented:
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?!?

Author

Commented:
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

Author

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial