lookup multiple column

[lezawang]

Hi
Can I do multiple column Vlookup. I searched the internet but most of examples are using Match-Index. I want to learn multiple Vlookup first then I will go to match-index. I am thinking of array function but do not know how to start

I have the table below. I want to do the following

Enter Name: John
Enter Item: Milk

Then excel will return 32

or excel will return 2 values the amount and date, so it will be 32 and mar

Is that possible? Thank you so much

[TABLE="class: grid, width: 276"]
<colgroup><col><col span="3"></colgroup><tbody>[TR]
[TD]name[/TD]
[TD]item[/TD]
[TD]amount[/TD]
[TD]month[/TD]
[/TR]
[TR]
[TD]mary[/TD]
[TD]milk[/TD]
[TD]59[/TD]
[TD]feb[/TD]
[/TR]
[TR]
[TD]mary[/TD]
[TD]water[/TD]
[TD]75[/TD]
[TD]jan[/TD]
[/TR]
[TR]
[TD]mary[/TD]
[TD]juice[/TD]
[TD]61[/TD]
[TD]feb[/TD]
[/TR]
[TR]
[TD]john[/TD]
[TD]milk[/TD]
[TD]32[/TD]
[TD]mar[/TD]
[/TR]
[TR]
[TD]john[/TD]
[TD]water[/TD]
[TD]1[/TD]
[TD]feb[/TD]
[/TR]
[TR]
[TD]jon[/TD]
[TD]juice[/TD]
[TD]73[/TD]
[TD]jan[/TD]
[/TR]
[TR]
[TD]alex[/TD]
[TD]milk[/TD]
[TD]60[/TD]
[TD]feb[/TD]
[/TR]
[TR]
[TD]alex[/TD]
[TD]water[/TD]
[TD]16[/TD]
[TD]mar[/TD]
[/TR]
[TR]
[TD]alex[/TD]
[TD]juice[/TD]
[TD]93[/TD]
[TD]feb[/TD]
[/TR]
[TR]
[TD]linda[/TD]
[TD]milk[/TD]
[TD]63[/TD]
[TD]jan[/TD]
[/TR]
[TR]
[TD]linda[/TD]
[TD]water[/TD]
[TD]19[/TD]
[TD]feb[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Book1
	A	B	C	D	E	F
1	name	item	amount	month		john
2	mary	milk	59	feb		milk
3	mary	water	75	jan
4	mary	juice	61	feb		32
5	john	milk	32	mar		mar
6	john	water	1	feb
7	jon	juice	73	jan
8	alex	milk	60	feb
9	alex	water	16	mar
10	alex	juice	93	feb
11	linda	milk	63	jan
12	linda	water	19	feb
Sheet1

Control+shift+enter…

=VLOOKUP(F1&"|"&F2,CHOOSE({1,2},$A$2:$A$12&"|"&$B$2:$B$12,$C$2:$C$12),2,0)

=VLOOKUP(F1&"|"&F2,CHOOSE({1,2},$A$2:$A$12&"|"&$B$2:$B$12,$D$2:$D$12),2,0)

 

[lezawang]

Thank you very much for your reply. Lets say I have a table like below.

So the user will enter the name and excel will return Amount and month
So I selected 2 cells (because I am expecting 2 values to be returned) then I typed the following in the first cell:

=VLOOKUP(A2,$A$1:$D$13,{3,4},FALSE)

and then pressed ctr+Shift+enter

But excel only returned the amount to me, 80, not the month?

How can I fix that. Thanks once again.

[TABLE="width: 256"]
<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64"]name[/TD]
[TD="class: xl63, width: 64"]item[/TD]
[TD="class: xl63, width: 64"]amount[/TD]
[TD="class: xl63, width: 64"]month[/TD]
[/TR]
[TR]
[TD="class: xl63"]mary1[/TD]
[TD="class: xl63"]milk[/TD]
[TD="class: xl63"]80[/TD]
[TD="class: xl63"]jan[/TD]
[/TR]
[TR]
[TD="class: xl63"]mary2[/TD]
[TD="class: xl63"]water[/TD]
[TD="class: xl63"]15[/TD]
[TD="class: xl63"]feb[/TD]
[/TR]
[TR]
[TD="class: xl63"]mary3[/TD]
[TD="class: xl63"]juice[/TD]
[TD="class: xl63"]54[/TD]
[TD="class: xl63"]mar[/TD]
[/TR]
[TR]
[TD="class: xl63"]mary4[/TD]
[TD="class: xl63"]milk[/TD]
[TD="class: xl63"]97[/TD]
[TD="class: xl63"]mar[/TD]
[/TR]
[TR]
[TD="class: xl63"]mary5[/TD]
[TD="class: xl63"]water[/TD]
[TD="class: xl63"]97[/TD]
[TD="class: xl63"]jan[/TD]
[/TR]
[TR]
[TD="class: xl63"]mary6[/TD]
[TD="class: xl63"]juice[/TD]
[TD="class: xl63"]79[/TD]
[TD="class: xl63"]jan[/TD]
[/TR]
[TR]
[TD="class: xl63"]mary7[/TD]
[TD="class: xl63"]milk[/TD]
[TD="class: xl63"]48[/TD]
[TD="class: xl63"]feb[/TD]
[/TR]
[TR]
[TD="class: xl63"]mary8[/TD]
[TD="class: xl63"]water[/TD]
[TD="class: xl63"]87[/TD]
[TD="class: xl63"]mar[/TD]
[/TR]
[TR]
[TD="class: xl63"]mary9[/TD]
[TD="class: xl63"]juice[/TD]
[TD="class: xl63"]14[/TD]
[TD="class: xl63"]mar[/TD]
[/TR]
[TR]
[TD="class: xl63"]mary10[/TD]
[TD="class: xl63"]milk[/TD]
[TD="class: xl63"]48[/TD]
[TD="class: xl63"]jan[/TD]
[/TR]
[TR]
[TD="class: xl63"]mary11[/TD]
[TD="class: xl63"]water[/TD]
[TD="class: xl63"]73[/TD]
[TD="class: xl63"]jan[/TD]
[/TR]
[TR]
[TD="class: xl63"]mary12[/TD]
[TD="class: xl63"]juice[/TD]
[TD="class: xl63"]18[/TD]
[TD="class: xl63"]feb[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Let F1 = mary1

In G1 control+shift+enter:

=VLOOKUP(F2,$A$2:$D$13,{3,4},0)

Since VLOOKUP cannot display two results in a single cell, you just see 80.

Select the formula in the formula bar and hit F9. You'll see:

{80,"jan"}

To display these values in 1 cell, try the following: Conrol+shift+enter...

=TEXTJOIN("; ",TRUE,VLOOKUP(F2,$A$2:$D$13,{3,4},0))

 

[XOR LX]

So I selected 2 cells (because I am expecting 2 values to be returned) then I typed the following in the first cell:

=VLOOKUP(A2,$A$1:$D$13,{3,4},FALSE)

and then pressed ctr+Shift+enter

But excel only returned the amount to me, 80, not the month?

That will work if you're selecting a horizontal range of two cells. If you're selecting a vertical range of two cells, however, you need:

=VLOOKUP(A2,$A$1:$D$13,{3;4},FALSE)

or, if you're not using an English-language version of Excel, whatever the vertical separator within array constants is for your locale.

Regards

 

[lezawang]

Thank you once again. I have 2 table like below. I selected 3 cells (C1:C3) and then I typed the following in cells C1


=A1:A3-B1:B3 (ctrt+alt+ent)


and I got the right answer in c1 to c3.


If I can do that, then why I can not do the same using Vlookup? Why I need TextJoin() function. Thank you very much.

[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"]1[/TD]
[TD="width: 64, align: right"]4[/TD]
[TD="width: 64, align: right"]-3[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]-3[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]-3[/TD]
[/TR]
</tbody>[/TABLE]

 

[XOR LX]

why I can not do the same using Vlookup?

You can. See my earlier reply.

Regards

 

[Aladin Akyurek]

Thank you once again. I have 2 table like below. I selected 3 cells (C1:C3) and then I typed the following in cells C1


=A1:A3-B1:B3 (ctrt+alt+ent)


and I got the right answer in c1 to c3.


If I can do that, then why I can not do the same using Vlookup? Why I need TextJoin() function. Thank you very much.

[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"]1[/TD]
[TD="width: 64, align: right"]4[/TD]
[TD="width: 64, align: right"]-3[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]-3[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]-3[/TD]
[/TR]
</tbody>[/TABLE]

We can use consecutive cells (instead of 1 cell TEXTJOIN creates)...

F2 = mary1

In G2 control+shift+enter, not just enter, and copy across:

=IFERROR(INDEX(VLOOKUP($F2,$A$2:$D$13,{3,4},0),COLUMNS($G2:G2)),"")

This puts the retrieved values in consecutive cells.

 

[lezawang]

Amazing, thank you all so so much.

 

[Aladin Akyurek]

Amazing, thank you all so so much.

Glad to help.