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

name	item	amount	month
mary	milk	59	feb
mary	water	75	jan
mary	juice	61	feb
john	milk	32	mar
john	water	1	feb
jon	juice	73	jan
alex	milk	60	feb
alex	water	16	mar
alex	juice	93	feb
linda	milk	63	jan
linda	water	19	feb

<colgroup><col><col span="3"></colgroup><tbody>
</tbody>

 

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

name	item	amount	month
mary1	milk	80	jan
mary2	water	15	feb
mary3	juice	54	mar
mary4	milk	97	mar
mary5	water	97	jan
mary6	juice	79	jan
mary7	milk	48	feb
mary8	water	87	mar
mary9	juice	14	mar
mary10	milk	48	jan
mary11	water	73	jan
mary12	juice	18	feb

<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>
</tbody>

 

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

1	4	-3
2	5	-3
3	6	-3

<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>

 

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

1	4	-3
2	5	-3
3	6	-3

<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>

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.