double lookup

oliver_penney

Board Regular
Joined
Dec 18, 2002
Messages
182
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?

thanx in advance
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
not sure this is what you really mean
=if(and(a1="aaa2",b1="bbb2"),"ccc3","not a match")
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Or..
Book1
ABCDEFG
1Field 1Field 2Field 3Field 1Field 2
2aaa1bbb1ccc1aaa2bbb2ccc3
3aaa1bbb2ccc2
4aaa2bbb2ccc3
Sheet5


Formula in G2,

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

HTH
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top