Lookup using 2 values

[ScreaminPain]

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

 

[ExcelMercy]

Could you provide some sample data?


I think an iferror + index/match would solve your issue.

 

[WarPigl3t]

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	no	what	is	up
2	pwn	yes	how	are	you
3	hell	no	what	is	up
4	word	cool	Hello	big	world

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

=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.

 

[WarPigl3t]

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.

=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.

 

[ScreaminPain]

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?

 

[FDibbins]

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​	no	what	is	up		up
3​	2​	pwn​	yes	how	are	you
4​	3​	hell​	no	what	is	up
5​	4​	word​	cool	Hello	big	world

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

 

[WarPigl3t]

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.

 

[Aladin Akyurek]

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:

=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.