Problem with Index and sumproduct function to vlookup a range with ranges

[vrajesh]

the formula in column F below

=IFERROR(IF(AND(E2>=$B$3,E2<=$C$9),INDEX($D$3:$D$9, SUMPRODUCT(--(E2<=$C$3:$C$9), --(E2>=$B$3:$B$9), ROW($A$1:$A$7))),"out of range"),"err")


Ideally for value 21 in column E the correct answer should be "err" which appears in row2 and row 10 but not in row 5? I am not able to resolve why?

Same is the case with value 109 in row 7 and row 15

but not in row

A B C D E F

Row2 21 err

Row3 10 20 A 11 A

Row4 30 40 B 141 out of range

Row5 50 60 C 21 C

Row6 70 80 D 39.6 B

Row7 90 100 E 109 E

Row8 110 120 F 51 C

Row9 130 140 G 77 D

Row10 21 err

Row11 131 G

Row12 55 C

Row13 11 A

Row14 0 out of range

Row15 109 err


Thanks in avance.

Vrajesh

 

[vrajesh]

The data is..


A B C D E F

Row2 Blank Blank Blnk 21 err

Row3 10 20 A 11 A

Row4 30 40 B 141 out of range

Row5 50 60 C 21 C

Row6 70 80 D 39.6 B

Row7 90 100 E 109 E

Row8 110 120 F 51 C

Row9 130 140 G 77 D

Row10 Blank Blank Blnk 21 err

Row11 Blank Blank Blnk 131 G

Row12 Blank Blank Blnk 55 C

Row13 Blank Blank Blnk 11 A

Row14 Blank Blank Blnk 0 out of range

Row15 Blank Blank Blnk 109 err

 

[Faseeh]

Hi vrajesh,


The range of no. that you have specified is exactly as that you have quoted or it is just for elaboration. I asked this question because if the range is continuous range like 10-20, 21-30, 31-40 etc then you can simply use vlookup. If it is not continuous then should sould resort to some other formula.


Regards,

 

[NARAYANK991]

Hi Rajesh ,


The problem is a peculiar one , and I cannot properly explain it. The reason is that the SUMPRODUCT function returns 0 , and INDEX(range,0) returns a column of data. This column of data in one cell returns unexplainable values. There seems to be some consistency in how Excel does things however !


In column G , in cell G2 , enter the formula =INDEX($D$3:$D$9,0) , and copy it downwards , and see the results.


The correct way to formulate your formula is to check for the SUMPRODUCT = 0 ; if so , it is an error. Try the following :


=IF(OR(E2<$B$3,E2>$C$9),"out of range",IF(SUMPRODUCT((--(E2<=$C$3:$C$9)*--(E2>=$B$3:$B$9))*(ROW($A$1:$A$7)))=0,"err",INDEX($D$3:$D$9,SUMPRODUCT(--(E2<=$C$3:$C$9),--(E2>=$B$3:$B$9),ROW($A$1:$A$7)))))


Narayan

 

[Faseeh]

Hi vrajesh,


Besides Narrayan's Formula this will also work assuming your data between A1:E15 and results in col.F

=IF(AND(E2>=$B$3,E2<=$C$9),IF(SUM((E2>=--$B$3:$B$9)*(E2<=--$C$3:$C$9)*ROW($A$1:$A$7))=0,"Err",INDEX($D$3:$D$9,SUM((E2>=--$B$3:$B$9)*(E2<=--$C$3:$C$9)*ROW($A$1:$A$7)),0)),"Out of Range")

Press Ctrl+Shift+Enter


@Narrayan, 13 Characters smaller then ur Formula :D Just Kiding


Regards,

Faseeh

 

[NARAYANK991]

Hi Faseeh ,


No problem , but my attempt was at using Rajesh's own formula ; it is far better to correct someone , than to present a different solution. If a different solution is given , then the original poster (OP) assumes that his / her formulae / solution are wrong , and nothing further can be done in that direction.


Hopefully my method will encourage the OP to continue thinking on his / her own. And clear any misunderstanding(s) there may be about Excel's functions.


Narayan

 

[Faseeh]

Hi Narrayn, your approach is correct i was just kidding.


Regards,

 

[vrajesh]

Thanks Narayan.


You are right the problem is peculiar.

It appears only when the vlookup formula in F column is in the same row where the

lookup data is situated.


Say our lookup data is in row A3 to E9 and lookup is done in colum F from row 2 to row 100, then row no.1 and rows from 10 to 100 will give correct result

but row 3 to 9 will give resepctive rows corresponding value of E in place of "err" which should have been the correct.


Hi Faeesh,

Thanks for your reply

The range as you asked is not continous but broken as in the illustration but it is sorted in ascending order. I tried the solution given by you but unfortunately its not working.


Regards

Vrajesh

 

[Faseeh]

Hi Rajesh,


Just out of interest why the formula didn't worked see this file:


http://dl.dropbox.com/u/60644346/vrejesh.xlsx


Regards,