Link to home
Create AccountLog in
Avatar of shilpi2
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
Avatar of barry houdini
barry houdini
Flag of United Kingdom of Great Britain and Northern Ireland image

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!
Avatar of shilpi2
shilpi2

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.
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?
Avatar of shilpi2

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
ASKER CERTIFIED SOLUTION
Avatar of jppinto
jppinto
Flag of Portugal image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of shilpi2

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