PDA

View Full Version : Finding Value Between Two Sets of Columns Plus Code



barim
06-12-2017, 09:42 AM
I am trying to pull value something similar to tax tables. If Value1 (2003) is greater 2001 and less than 3000 and the code is letís say AB, pull the value from AB column. Same thing is with Value2.
Value1 and Value2 have to satisfy criteria before value of the code can be pulled over to cell M2.
For code AC, 3050 is between 3001 and 4000 and 550 is between 501 and 600 and it is pulling value of 90 into cell M3
Attached is sample workbook.

Thank you in advance.

xld
06-12-2017, 10:36 AM
I don't agree with your result for AB, I get 10, but try this formula


=INDEX(IF($J2="AB",$E$1:$E$10,$F$1:$F$10),MIN(IF(($A$1:$A$10<=$K2)*($K2<=$B$1:$B$10)*($C$1:$C$10<=$L2)*($L2<=$D$1:$D$10),ROW($A$1:$A$10))))

It is an array formula, so commit with Ctrl-Shift-Enter.

barim
06-13-2017, 09:31 AM
xld, thank you so much for your help on this formula. It works for me.:clap: