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)

TYPE	FROM	TO	CLS		TYPE	DATA	CLS
AA	0	79999	D		AA	56000	D
AA	80000	119999	C		AA	95000	C
AA	120000	169999	B		AA	130000	B
AA	170000	9999999	A		AA	250000	A
AB	0	100000	D		AB	50000
AB	100001	300000	C		AB	200000
AB	300001	500000	B		AB	450000
AB	500001	9999999	A		AB	600000
AC	0	500000	D		AB	715965
AC	500001	750000	C		AB	243983
AC	750001	950000	B		AC	250000
AC	950001	9999999	A		AC	708492
					AC	1278067
					AC	850000
					AC	440773
					AC	566173

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

 

[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

<tbody>
</tbody>

 

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