# LOOKUP AND()

#### PM1

##### Board Regular
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

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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,

PM1 said:
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!

PM1 said:
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
ABCD
14123456123456 Description
Sheet2

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

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

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

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.

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

PM1 said:
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?

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

Replies
8
Views
160
Replies
2
Views
82
Replies
1
Views
184
Replies
3
Views
120
Replies
2
Views
162

1,203,658
Messages
6,056,583
Members
444,877
Latest member
kat517

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back