# Index & Match in Excel

I am trying to use Index and Match to lookup a value in my worksheet but I'm having trouble getting the formula to work correctly. Here's the scenario

Column F is the lookup column that I want to return the value from.

Column B is the first column that I need to match up exactly to my value in cell L6. The next column is Column C and I need to have the value in cell M6 be greater than or equal to column C.

This is what I have so far and it works but only if the value in column C is exact. I need it to be modified to say it either equals the value or is greater than the value in column C.

``````{=INDEX('Raw Matl-CT'!\$F\$6:\$F\$200,MATCH(1,('Quote Pricing Calculator'!\$L\$6='Raw Matl-CT'!\$B\$6:\$B\$200)*('Quote Pricing Calculator'!\$M\$6='Raw Matl-CT'!\$C\$6:\$C\$200),0))}
``````

Column B            Column C            Column F

303                       .1250                    \$1.00
303                       .1300                    \$2.00
303                       .1310                    \$3.00
303                       .1875                    \$4.00
303                       .2500                    \$5.00
303                       .3750                    \$6.00

Value in Cell L6 = 303
Value in Cell M6 = .3125

The value that should be returned in the above example should be: \$5.00 since the value in M6 is greater than .2500
LVL 1
###### Who is Participating?

Commented:
Check attached
Sample1.xlsx
0

Commented:
HI,
pls try as an array formula (Ctrl-Shift-Enter)

``````=INDEX('Raw Matl-CT'!\$F\$6:\$F\$200,MATCH('Quote Pricing Calculator'!\$M\$6,--(('Quote Pricing Calculator'!\$L\$6='Raw Matl-CT'!\$B\$6:\$B\$200)*('Raw Matl-CT'!\$C\$6:\$C\$200)),1))
``````
Regards
0

Information Technology ManagerAuthor Commented:
That didn't work. No matter what value I put in M6 the value in the cell where the formula is doesn't change. It's picking a value that isn't even close to the row it should be returning.
0

Commented:
Try this

``````=INDEX('Raw Matl-CT'!\$F\$6:\$F\$200,COUNTIFS('Raw Matl-CT'!\$B\$6:\$B\$200,'Quote Pricing Calculator'!\$L\$6,'Raw Matl-CT'!\$C\$6:\$C\$200,"<"&'Quote Pricing Calculator'!\$M\$6),1)
``````
0

Information Technology ManagerAuthor Commented:
Nope. Same issue. It just grabs a value not even close and then doesn't change no matter what I enter in M6 for a value.
0

Information Technology ManagerAuthor Commented:
Here is a sample of the file. The last formula posted is what I have in my cell R6
Sample.xlsx
0

Information Technology ManagerAuthor Commented:
It seems to work somewhat but when you put .2500 in it the price should be \$3.01 but it's showing it as \$3.53. It should be equal to or greater to the value in column C.
0

Commented:
Try the following:
``````=INDEX(F2:F7,MATCH(1,INDEX((B2:B7=L6)*(C2:C7>=M6),0),0),1)
``````
0

Information Technology ManagerAuthor Commented:
I just modified your sample file to add = to the last match and it works fine. It needed to be equal to OR greater than. Thanks for the help!
0

Commented:
Lawrence: You could also try mine. Simpler and includes EQUAL OR.
0

Information Technology ManagerAuthor Commented:
Sorry, I didn't see yours till just now. I will test yours and post back.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.