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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
not sure this is what you really mean
=if(and(a1="aaa2",b1="bbb2"),"ccc3","not a match")
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,461
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 18, 2002
Messages
182
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
Joined
May 19, 2002
Messages
8,458

ADVERTISEMENT

try this
=INDEX(C1:C3,MATCH(A1,A1:A3,0),MATCH(B1,B1:B3,0))
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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
Joined
Aug 31, 2005
Messages
5,828

ADVERTISEMENT

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.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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
Joined
Feb 28, 2003
Messages
2,615
Or..
Book1
ABCDEFG
1Field 1Field 2Field 3Field 1Field 2
2aaa1bbb1ccc1aaa2bbb2ccc3
3aaa1bbb2ccc2
4aaa2bbb2ccc3
Sheet5


Formula in G2,

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

HTH
 

Forum statistics

Threads
1,136,878
Messages
5,678,302
Members
419,753
Latest member
Sallylwy

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
Top