Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 62
  • Last Modified:

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))}

Open in new window


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
0
Lawrence Salvucci
Asked:
Lawrence Salvucci
  • 6
  • 2
  • 2
  • +1
3 Solutions
 
Rgonzo1971Commented:
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))

Open in new window

Regards
0
 
Lawrence SalvucciInformation 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
 
Ejgil HedegaardCommented:
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)

Open in new window

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Lawrence SalvucciInformation 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
 
Lawrence SalvucciInformation 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
 
Ejgil HedegaardCommented:
Check attached
Sample1.xlsx
0
 
Lawrence SalvucciInformation 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
 
AL_XResearchCommented:
Try the following:
=INDEX(F2:F7,MATCH(1,INDEX((B2:B7=L6)*(C2:C7>=M6),0),0),1)

Open in new window

0
 
Lawrence SalvucciInformation 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
 
AL_XResearchCommented:
Lawrence: You could also try mine. Simpler and includes EQUAL OR.
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Sorry, I didn't see yours till just now. I will test yours and post back.
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 6
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now