additional condition in vlookup

[sachin483]

currently i am using the below formula for in between range but i want add an additional column (type) for verifying how can it be done
=VLOOKUP(G2,B:D,3,TRUE)

[TABLE="width: 512"]
<colgroup><col width="64" span="8" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]TYPE[/TD]
[TD="class: xl65, width: 64"]FROM[/TD]
[TD="class: xl65, width: 64"]TO[/TD]
[TD="class: xl65, width: 64"]CLS[/TD]
[TD="width: 64"][/TD]
[TD="class: xl65, width: 64"]TYPE[/TD]
[TD="class: xl66, width: 64"]DATA[/TD]
[TD="class: xl66, width: 64"]CLS[/TD]
[/TR]
[TR]
[TD="class: xl63"]AA[/TD]
[TD="class: xl64"]0[/TD]
[TD="class: xl63"]79999[/TD]
[TD="class: xl63"]D[/TD]
[TD][/TD]
[TD="class: xl67"]AA[/TD]
[TD="class: xl66, align: right"]56000[/TD]
[TD="class: xl67"] D[/TD]
[/TR]
[TR]
[TD="class: xl63"]AA[/TD]
[TD="class: xl64"]80000[/TD]
[TD="class: xl63"]119999[/TD]
[TD="class: xl63"]C[/TD]
[TD][/TD]
[TD="class: xl67"]AA[/TD]
[TD="class: xl66, align: right"]95000[/TD]
[TD="class: xl67"] C[/TD]
[/TR]
[TR]
[TD="class: xl63"]AA[/TD]
[TD="class: xl64"]120000[/TD]
[TD="class: xl63"]169999[/TD]
[TD="class: xl63"]B[/TD]
[TD][/TD]
[TD="class: xl67"]AA[/TD]
[TD="class: xl66, align: right"]130000 [/TD]
[TD="class: xl67"]B[/TD]
[/TR]
[TR]
[TD="class: xl63"]AA[/TD]
[TD="class: xl64"]170000[/TD]
[TD="class: xl63"]9999999[/TD]
[TD="class: xl63"]A[/TD]
[TD][/TD]
[TD="class: xl67"]AA[/TD]
[TD="class: xl66, align: right"]250000[/TD]
[TD="class: xl67"] A[/TD]
[/TR]
[TR]
[TD="class: xl63"]AB[/TD]
[TD="class: xl64"]0[/TD]
[TD="class: xl63"]100000[/TD]
[TD="class: xl63"]D[/TD]
[TD][/TD]
[TD="class: xl67"]AB[/TD]
[TD="class: xl66, align: right"]50000[/TD]
[TD="class: xl67"] [/TD]
[/TR]
[TR]
[TD="class: xl63"]AB[/TD]
[TD="class: xl64"]100001[/TD]
[TD="class: xl63"]300000[/TD]
[TD="class: xl63"]C[/TD]
[TD][/TD]
[TD="class: xl67"]AB[/TD]
[TD="class: xl66, align: right"]200000[/TD]
[TD="class: xl67"] [/TD]
[/TR]
[TR]
[TD="class: xl63"]AB[/TD]
[TD="class: xl64"]300001[/TD]
[TD="class: xl63"]500000[/TD]
[TD="class: xl63"]B[/TD]
[TD][/TD]
[TD="class: xl67"]AB[/TD]
[TD="class: xl66, align: right"]450000[/TD]
[TD="class: xl67"] [/TD]
[/TR]
[TR]
[TD="class: xl63"]AB[/TD]
[TD="class: xl64"]500001[/TD]
[TD="class: xl63"]9999999[/TD]
[TD="class: xl63"]A[/TD]
[TD][/TD]
[TD="class: xl67"]AB[/TD]
[TD="class: xl66, align: right"]600000[/TD]
[TD="class: xl67"] [/TD]
[/TR]
[TR]
[TD="class: xl63"]AC[/TD]
[TD="class: xl64"]0[/TD]
[TD="class: xl63"]500000[/TD]
[TD="class: xl63"]D[/TD]
[TD][/TD]
[TD="class: xl67"]AB[/TD]
[TD="class: xl66, align: right"]715965[/TD]
[TD="class: xl67"] [/TD]
[/TR]
[TR]
[TD="class: xl63"]AC[/TD]
[TD="class: xl64"]500001[/TD]
[TD="class: xl63"]750000[/TD]
[TD="class: xl63"]C[/TD]
[TD][/TD]
[TD="class: xl67"]AB[/TD]
[TD="class: xl66, align: right"]243983[/TD]
[TD="class: xl67"] [/TD]
[/TR]
[TR]
[TD="class: xl63"]AC[/TD]
[TD="class: xl64"]750001[/TD]
[TD="class: xl63"]950000[/TD]
[TD="class: xl63"]B[/TD]
[TD][/TD]
[TD="class: xl67"]AC[/TD]
[TD="class: xl66, align: right"]250000[/TD]
[TD="class: xl67"] [/TD]
[/TR]
[TR]
[TD="class: xl63"]AC[/TD]
[TD="class: xl64"]950001[/TD]
[TD="class: xl63"]9999999[/TD]
[TD="class: xl63"]A[/TD]
[TD][/TD]
[TD="class: xl67"]AC[/TD]
[TD="class: xl66, align: right"]708492[/TD]
[TD="class: xl67"] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl67"]AC[/TD]
[TD="class: xl66, align: right"]1278067[/TD]
[TD="class: xl67"] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl67"]AC[/TD]
[TD="class: xl66, align: right"]850000[/TD]
[TD="class: xl67"] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl67"]AC[/TD]
[TD="class: xl66, align: right"]440773[/TD]
[TD="class: xl67"] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl67"]AC[/TD]
[TD="class: xl66, align: right"]566173[/TD]
[TD="class: xl67"] [/TD]
[/TR]
</tbody>[/TABLE]

 

[NARAYANK991]

Hi ,

If we assume that the table on the left hand side is in the range A2:D14 , and the one on the right is in the range F2:H18 , then in H3 , enter the following formula , and copy down :

=INDEX($D$3:$D$14, SUMPRODUCT(($A$3:$A$14 = F3) * ($B$3:$B$14 <= G3) * ($C$3:$C$14 >= G3), ROW($A$3:$A$14) - MIN(ROW($A$3:$A$14)) + 1))

 

[inactiveuserps07]

@sachin483, here's another approach if you'd like (assuming headers are in row 1):

=INDEX($D$2:$D$13,SUMPRODUCT(MATCH(1,($A$2:$A$13=F2)*($C$2:$C$13>=G2),0)))

Notice that the lower limits in Column B are not necessary in this approach.

 

[Aladin Akyurek]

Control+shift+enter, not just enter, and copy down:

=VLOOKUP(G2,IF($A$2:$A$13=F2,$B$2:$D$13),3,1)

Since the match-type = 1, i.e. TRUE, this set up should be faster. Moreover, it doesn't confound retrieval anf aggregation (summing).

 

[sachin483]

thanks @NARAYANK991 and @ErikTyler formula working fine but while using formula of @Aladin Akyurek cls not reflecting properly eg:-

for AA -130000 cls "D" reflecting instead of "B" in the grid

[TABLE="width: 192"]
<tbody>[TR]
[TD="class: xl64, width: 64"][/TD]
[TD="class: xl63, width: 64, align: right"][/TD]
[TD="class: xl64, width: 64"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

[...]

using formula of @Aladin Akyurek cls not reflecting properly eg:-

for AA -130000 cls "D" reflecting instead of "B" in the grid
[...]

Over here I get B for the AA and 130000 conditions. Did you apply control+shift+enter, instead of just enter in H2 and then copied down?

 

[sachin483]

sorry my mistake @Aladin Akyurek i just enter not control+shift_enter , thanks a lot

 

[Aladin Akyurek]

sorry my mistake @Aladin Akyurek i just enter not control+shift_enter , thanks a lot

No problem. You are welcome.