Lookup using 2 values

ScreaminPain

Board Regular
Joined
Feb 8, 2009
Messages
58
COLUMN "A" HAS A LIST OF DATES.
COLUMN "B" HAS A LIST OF NAMES (MANY DUPLICATED)
COLUMNS "C" THRU "AA" HAS DESCRIPTIVE DATA

On another sheet I need to import data found in column "D" (and others) that matches BOTH the date in column "A" AND name in column "B" and return data from that row......


FYI:
The dates are listed in column "A" are in desending order and the NAMES in column "B" are in random order....
 

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.
Could you provide some sample data?


I think an iferror + index/match would solve your issue.
 
Upvote 0
I'm sure you already know about the VLookUp function. You can lookup 1 value in a range of cells. But you want to lookup two values. Well we are going to use the same lookup function. The only difference is, you are not going to press enter after entering the formula. You are going to press Control Shift Enter.
A
B
C
D
E
1
hell
nowhat
isup
2
pwnyeshowareyou
3
hell
nowhatisup
4
wordcoolHellobigworld

<tbody>
</tbody>

In the table above, Cells A1 and B1 are my lookup values. I want to find a row in column A that says "hell". It also needs to be a row in column B that says "no". I highlighted those cells orange in the table above.

Now the green text in the table above is where I put this formula. Specifically, I put it in cell C1.
Code:
=VLOOKUP(A1:B1,A2:E4,3,FALSE)
When you press Control Shift Enter, it will have brackets like this...
{=VLOOKUP(A1:B1,A2:E4,3,FALSE)}
I used an easy example so you can learn because if I try to explain it to you through the dataset that you provided, you would get confused. How this helps.
 
Last edited:
Upvote 0
Actually that last formula I gave you sucks because you'll have to change the output range for each column. Use this one instead. That way you only need to type in 1 formula.
Code:
=VLOOKUP($A1:$B1,$A$2:$E$4,COLUMN(C1),FALSE)
Also you notice I used dollar signs this time. Those are absolute references. You should use them or you might encounter some errors later on in your real excel file. Pay close attention to where I placed the dollar signs. Make sure you place them in the same places for your dataset.
 
Last edited:
Upvote 0
Thank You WarPiglet for your response

However, your suggestion returns a "#value" error. It seems as if it should work, but Excel doesn't like the initial "lookup" value ($A1:$B1).

Is it possible to construct a formula using MATCH & INDEX that would do the same thing as VLOOKUP?
 
Upvote 0
Using WarPig's table, searching in A and B and returning D...

A​
B​
C​
D​
E​
F​
G​
H​
1​
A
B
C
D
E
2​
1
hell
nowhatisupup
3​
2
pwn
yeshowareyou
4​
3
hell
nowhatisup
5​
4
word
coolHellobigworld

H2=INDEX($F$2:$F$5,MATCH("Hell"&"-"&"No",INDEX($B$2:$B$5&"-"&C2:C5,0),0))
regular formula, no array needed

You can hard-code the text, or reference it from other cells
 
Upvote 0
Thank You WarPiglet for your response

However, your suggestion returns a "#value" error. It seems as if it should work, but Excel doesn't like the initial "lookup" value ($A1:$B1).

Is it possible to construct a formula using MATCH & INDEX that would do the same thing as VLOOKUP?

can you paste the vlookup formula that you tried using your dataset so I can see what you did? I'm pretty sure the problem isn't the formula.
 
Upvote 0
COLUMN "A" HAS A LIST OF DATES.
COLUMN "B" HAS A LIST OF NAMES (MANY DUPLICATED)
COLUMNS "C" THRU "AA" HAS DESCRIPTIVE DATA

On another sheet I need to import data found in column "D" (and others) that matches BOTH the date in column "A" AND name in column "B" and return data from that row......


FYI:
The dates are listed in column "A" are in desending order and the NAMES in column "B" are in random order....

Let Sheet1, A:AA, house the data.

Sheet2

A1 houses a relevant date as look up value.
A2 houses a relevant name as look up value.

1. Return just what is in column D of Sheet1 corresponding to the above look up values.

A4, control+shift+enter, not just enter:
Rich (BB code):
=IFERROR(INDEX(Sheet1!$D$2:$D$400,
  MATCH(A$1,IF(Sheet1!$B$2:$B$400=A$2,Sheet1$A$2:$A$400),0)),"Not Found")
Control+shift+enter means: Press down the control and the shift keys at the same time, while you hit the enter key.

2. If you intend to return multiple records from column D of Sheet1, we need a different set up

3. If you inted to return records from column D of Sheet1 as well as other columns, again we need a differen set up for efficiency reasons.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,543
Latest member
MartinLarkin

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