shilpi2

asked on

# 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

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

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

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

I already provided a formula:

=LOOKUP(2,1/((Sheet2!A1:A1

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!

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!

ASKER

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.

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))

=IF(ISNA(LOOKUP(2,1/((Shee

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?

ASKER

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

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

ASKER CERTIFIED SOLUTION

membership

Create an account to see this answer

Signing up is free. No credit card required.

ASKER

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

When i change the formula to

=LOOKUP(2,1/((Sheet1!I:I='

I get error #NUM.

Unable to adjust for dynamic array.

Thanks

Shilpi

=LOOKUP(2,1/(Sheet2!$A$3:$

and drag that formula across to C3

regards, barry