LOOKUP AND()

[PM1]

Is it possible to do a lookup on 2 columns so the results have to be exact before it returns a result?.

In Sheet 1 - A3 - I have "123456 Description"
In Sheet 1 - B3 - I have 4
In Sheet 1 - C3 - I have 1000

In Sheet 2 - A1 - I have 4
In Sheet 2 - B1 - I have "123456"

In Sheet 2 - C1 I want to lookup A1 + B1 in Sheet 1 and return C3

 

[Barrie Davidson]

Use the SUMPRODUCT function. Something like:

=SUMPRODUCT(('Sheet1'!$B$1:$B$500=A1)+0,('Sheet1'!$A$1:$A$500=B1)+0,'Sheet1'!$C$1:$C$1000)

Regards,

 

[Brian from Maui]

Is it possible to do a lookup on 2 columns so the results have to be exact before it returns a result?.

In Sheet 1 - A3 - I have "123456 Description"
In Sheet 1 - B3 - I have 4
In Sheet 1 - C3 - I have 1000

In Sheet 2 - A1 - I have 4
In Sheet 2 - B1 - I have "123456"

In Sheet 2 - C1 I want to lookup A1 + B1 in Sheet 1 and return C3

Concatenate columns A and B of sheet 1 say in column D with

=A3&CHAR(127)&B3 copy down

In sheet 2 in C1 enter,

=INDEX(Sheet1!C3:C100,MATCH(A1&CHAR(127)&B1,Sheet1!D3:D100,0))

Hey Barrie!

Use your deck lately?

 

[Yogi Anand]

Is it possible to do a lookup on 2 columns so the results have to be exact before it returns a result?.

In Sheet 1 - A3 - I have "123456 Description"
In Sheet 1 - B3 - I have 4
In Sheet 1 - C3 - I have 1000

In Sheet 2 - A1 - I have 4
In Sheet 2 - B1 - I have "123456"

In Sheet 2 - C1 I want to lookup A1 + B1 in Sheet 1 and return C3

Hi PM1:

I am sure the scope of your problem is much wider than in your example. Anyway, following is one way ...

Book2
	A	B	C	D
1	4	123456	123456 Description
Sheet2

formula in cell C1 is ... =VLOOKUP(Sheet2!$A$3&"*"&Sheet2!$B$3,Sheet1!$A$3:$A$3,1,0)

 

[PM1]

OK - I've arrived at

SUMPRODUCT(('BP²I PO''s'!A2:A269='BP²I Assets'!O2)+0,('BP²I PO''s'!B2:B269=(LEFT('BP²I Assets'!K2,6)+0,'BP²I PO''s'!D2:D269))

but I get an error in the formula = WHERE

I have to use =(LEFT('BP²I Assets'!K2,6) because its only the first 6 chrs that are in ('BP²I PO''s'!B2:B269

 

[Barrie Davidson]

Try

SUMPRODUCT(('BP²I PO''s'!A2:A269='BP²I Assets'!O2)+0,('BP²I PO''s'!B2:B269=LEFT('BP²I Assets'!K2,6))+0,'BP²I PO''s'!D2:D269)

Note that I've changed the location of two ending parentheses ")".

 

[Brian from Maui]

As a caveat to Sumproduct, it'll only return numeric values. Sumproduct, IMHO, should only be used for multi conditional counting and/or summing. Using Index/Match or Vloolup as Yogi suggested maybe be a better alternative. In the future, if you have text values to return, the latter suggestions will work.

 

[PM1]

Barrie, That produced a Value of 0, it seems not to be picking up the Values from ,'BP²I PO''s'!D2:D269

I actually posted this problem earlier today at

http://www.mrexcel.com/board2/viewtopic.php?t=182250&highlight= - but I think I was told off so I've tried to make it simpler.



Brian (from Maui) :wink: , it is values that I'm trying to bring back into the sheet

 

[Brian from Maui]

Brian (from Maui) :wink: , it is values that I'm trying to bring back into the sheet

Have you tried Yogi's or my suggestions?

 

[PM1]

Brian,

Tried Yogi's but got N/A - I cannot work out the logic of the formula VLOOKUP(O3&"*"&P3,'BP²I PO''s'!A1:D4,4,0) - if in my case O3 = 4 and P3 = 292889, how can it be found in A1:D4 ? - I know within that range A1 holds (O3) 4 and B1 holds (P3) 292889 and the result I want is in D1 but I cannot see how the lookup finds the 2 values in that range.

I also tried yours but again I got 0