lookup

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!

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

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