lookup

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.

Thanks

Shilpi
Book1.xls
barry houdini

Hello Shilpi, try this formula in B3

=LOOKUP(2,1/(Sheet2!\$A\$3:\$A\$100=\$A3)/(Sheet2!\$D\$3:\$D\$100="UI"),Sheet2!B\$3:B\$100)

and drag that formula across to C3

regards, barry
This is a duplicated question of this:

https://www.experts-exchange.com/questions/27044124/vlookup.html

I already provided a formula:

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

and a sample file for this question! You shouldn't post duplicated questions!

jppinto
If you feel that the previous question is already answered and you want to make a new question based on that, you should first close the previous question and only then make a new one.

If you feel that the first question is not answered yet, you should wait until you get a proper answer before opening a new question that is basically the same as the first!
shilpi2

How do i check whether the result returned by this function is NA or has some value?

Thanks for the replies . Pinto I will close the previous question and assign points. Thanks.
Use the ISNA() function, someething like this:

=IF(ISNA(LOOKUP(2,1/((Sheet2!A1:A12=Sheet1!A2)*(Sheet2!B1:B12=Sheet1!B2)),Sheet2!C1:C12)),"",LOOKUP(2,1/((Sheet2!A1:A12=Sheet1!A2)*(Sheet2!B1:B12=Sheet1!B2)),Sheet2!C1:C12))
The ISNA() function checks if the LOOKUP() function returns a #N/A error value. So this formula will check if the LOOKUP() function returns #N/A. If it does, the cell will be empty. If it doesn't it will show the result of the LOOKUP function.
Any question?

Thanks,

One more question !

What if my array is dynamic. The sheet1 from where  get data is expanding as it is connected to a database. How iwill I incorporate this?

Thanks

Shilpi
jppinto

membership
Create an account to see this answer
Signing up is free. No credit card required.

Hi Pinto,

When i change the formula to

=LOOKUP(2,1/((Sheet1!I:I='10-30sBM_Return'!A4)*(Sheet1!J:J="IPCHFI")),Sheet1!\$E\$5:\$E\$165)

I get error #NUM.

Unable to adjust for dynamic array.

Thanks

Shilpi