formula require...

[VBABEGINER]

hi all,

I need formula, where lookup values are in two different columns, and with combination of these 2 values I want to find an answer....

[TABLE="width: 453"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[TD]Column E[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]L[/TD]
[TD]p[/TD]
[TD]q[/TD]
[TD]r[/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]L1[/TD]
[TD]10[/TD]
[TD]40[/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD]31[/TD]
[TD]L2[/TD]
[TD]20[/TD]
[TD]50[/TD]
[TD]70[/TD]
[/TR]
[TR]
[TD]31[/TD]
[TD]L1[/TD]
[TD]30[/TD]
[TD]60[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Value to find[/TD]
[TD][/TD]
[TD]Answer Require..[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]31[/TD]
[TD]L1[/TD]
[TD="align: right"]80[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I use this formula, but these is something wrong..

=VLOOKUP(AND(A4,B4),A2:E4,3,FALSE)

 

[Scott T]

If you are always looking in column E

Excel 2010

	A	B	C	D	E
E	L	p	q	r
L1
L2
L1
value o find	answer
l1

<colgroup><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]30[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]60[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]31[/TD]

[TD="align: right"]20[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]70[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]31[/TD]

[TD="align: right"]30[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]80[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]31[/TD]

[TD="align: right"]80[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C7[/TH]
[TD="align: left"]{=INDEX(E2:E4,MATCH(A7&B7,A2:A4&B2:B4,0))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[snjpverma]

Is there a way to do this without using array? Just curious to know alternative ways of doing this.

 

[bosco_yip]

Try,

=VLOOKUP(A4&B4,IF({1,0},INDEX(A1:A4&B1:B4,0),E1:E4),2,FALSE)

Regards
Bosco

 

[Scott T]

Is there a way to do this without using array? Just curious to know alternative ways of doing this.

You could use a helper column to concatenate the two columns to avoid and array formula.

Excel 2010
	A	B	C	D	E	F
1	E	L	p	q	r	helper
2	30	L1	10	40	60	30L1
3	31	L2	20	50	70	31L2
4	31	L1	30	60	80	31L1
5
6	value o find		answer
7	31	l1	80
Sheet4

Cell Formulas
Range	Formula
F2	=A2&B2
F3	=A3&B3
F4	=A4&B4
C7	=INDEX(E2:E4,MATCH(A7&B7,F2:F4,0))

 

[bosco_yip]

Or, using post #2 table, in C7 :

=SUMIFS(E2:E4,A2:A4,A7,B2:B4,B7)

Regards
Bosco

 

[VBABEGINER]

I really dont understand how this works..?
Simple enter..nothing giving..
ctr shft enter ... giving me answer...I understand that this is array technology...but how...

If you are always looking in column E

Excel 2010

	A	B	C	D	E
E	L	p	q	r
L1
L2
L1
value o find	answer
l1

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]30[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]60[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]31[/TD]

[TD="align: right"]20[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]70[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]31[/TD]

[TD="align: right"]30[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]80[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]31[/TD]

[TD="align: right"]80[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10"]C7[/TH]
[TD="align: left"]{=INDEX(E2:E4,MATCH(A7&B7,A2:A4&B2:B4,0))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[VBABEGINER]

I really dont understand, how this too work???

it giving my answer..but worked...how..?

some Questions..

1. what IF is doing here..
2. Why column index taken 2, wherein it should be 3?

Try,

=VLOOKUP(A4&B4,IF({1,0},INDEX(A1:A4&B1:B4,0),E1:E4),2,FALSE)

Regards
Bosco

 

[VBABEGINER]

Can you please explain why it require, ctr shft entr to press..

If you are always looking in column E

 

[bosco_yip]

I really dont understand, how this too work???

it giving my answer..but worked...how..?

some Questions..

1. what IF is doing here..
2. Why column index taken 2, wherein it should be 3?

=VLOOKUP(A4&B4,IF({1,0},INDEX(A2:A4&B2:B4,0),E2:E4),2,FALSE)

>>

=VLOOKUP("31L1",IF({1,0},{"30L1";"31L2";"31L1"},{60;70;80}),2,FALSE)

>>

=VLOOKUP("31L1",{"30L1",60;"31L2",70;"31L1",80},2,FALSE)

>>

=80

Regards
Bosco