# double lookup

#### oliver_penney

##### Board Regular
if i have a table

aaa1 bbb1 ccc1
aaa1 bbb2 ccc2
aaa2 bbb2 ccc3

how do i do a

DOUBLEMATCH(aaa2,bbb2)=ccc3

type of formula to give me the first instance of the third column where the first two columns match certain criteria?

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

#### texasalynn

##### Well-known Member
not sure this is what you really mean
=if(and(a1="aaa2",b1="bbb2"),"ccc3","not a match")

#### Peter_SSs

##### MrExcel MVP, Moderator
oliver_penney

I am also not certain this is what you mean, but I would add a helper column.

Formula in D1 (copied down): =A1&"|"&B1
G3: =INDEX(C1:C5,MATCH(G1&"|"&G2,D1:D5,0))
Mr Excel.xls
ABCDEFGH
1aaa1bbb1ccc1aaa1|bbb1Col A Valueaaa2
2aaa1bbb2ccc2aaa1|bbb2Col B Valuebbb2
3aaa2bbb2ccc3aaa2|bbb2Col C Valueccc3
4
Double Lookup

#### oliver_penney

##### Board Regular
oliver_penney

I am also not certain this is what you mean, but I would add a helper column.

Formula in D1 (copied down): =A1&"|"&B1
G3: =INDEX(C1:C5,MATCH(G1&"|"&G2,D1:D5,0))

hi

thanx, yeah that would work with a helper column....was just wondering if there were a way to do it without

ta anyway

ol

#### texasalynn

##### Well-known Member
try this
=INDEX(C1:C3,MATCH(A1,A1:A3,0),MATCH(B1,B1:B3,0))

#### Richard Schollar

##### MrExcel MVP
I feel the following is an unnecessarily complicated solution, but it seems to work (helper column is so much cimpler though):
Book1
ABCD
114A
216B
318C
425D
522E
622F
7345G
8367H
934I
10
11CritACritBColCReturn
1222E
Sheet1

Richard

#### NBVC

##### Well-known Member
You could also probably revise PeterSSs's formula to:

=INDEX(C1:C5,MATCH(G1&"|"&G2,A1:A5&"|"&B1:B5,0))

adjust ranges to suit, then confirm it with CTRL+SHIFT+ENTER not just ENTER...you will see {} brackets around the formula if done correctly.

This needs no helper column.

##### MrExcel MVP
oliver_penney

I am also not certain this is what you mean, but I would add a helper column.

Formula in D1 (copied down): =A1&"|"&B1
G3: =INDEX(C1:C5,MATCH(G1&"|"&G2,D1:D5,0))

hi

thanx, yeah that would work with a helper column....was just wondering if there were a way to do it without

ta anyway

ol

=INDEX(\$C\$1:\$C\$5,MATCH(1,IF(\$A\$1:\$A\$5="aaa2",IF(\$B\$1:\$B\$5="bbb2",1)),0))

which you need to confirm with control+shift+enter, not just with enter.

#### Krishnakumar

##### Well-known Member
Or..
Book1
ABCDEFG
1Field 1Field 2Field 3Field 1Field 2
2aaa1bbb1ccc1aaa2bbb2ccc3
3aaa1bbb2ccc2
4aaa2bbb2ccc3
Sheet5

Formula in G2,

=DGET(A1:C4,3,E1:F2)

HTH

Replies
10
Views
385
Replies
12
Views
592
Replies
3
Views
601
Replies
1
Views
427
Replies
8
Views
233

1,171,720
Messages
5,877,125
Members
433,233
Latest member
Haripriya

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