Index match lookup problems

Simi

New member
Joined
Feb 10, 2012
Messages
190
Reaction score
0
Points
0
Location
Utah, USA
Excel Version(s)
Version 2002 Build 12527.20194
I am having trouble with an Index/Match look up. I am entering 2 values and to check against, then return the 3rd value from the lookup table.
I am trying to get it so the input doesn't have to match the lookup table exactly. I have attached a woorkbook with an example.View attachment Index-Match-help.xlsx
The table on the left which searches for depth 2 1/2, width as noted isn't working properly.

The results I want should be as follows.
if depth is 2 1/2 and width is <= 6 3/16 use 0.822
if depth is 2 1/2 and width is > 6 3/16 <= 7 3/8 use .980
if depth is 3 and width is <= 6 3/16 use 0.986
if depth is 3 and width is > 6 3/16 <= 7 3/8 use .1.176
 
Last edited:
Hello Simi
Ive spent a bit of time on your sheet, and the problems I found are:
1. Your look-up array for the Match function is converted to text values, which can cause unexpected matching results.
2. Also if Match is to produce accurate results where there isn't an exact match, the array has to be sorted in sequence:
Ascending to match below the lookup value ; Descending to match above.
So I tried various methods, and I got the best results as follows:
1. I created an additional column in the lookup table to add together the depth and width to use in the MATCH function
2. I sorted the table Cols (I to L) by this new numeric (Col K) im descending order instead of your ascending.
3. I then adjusted the array formulae to take into account the new column.

The results aren't bad. All those you have stipulated above are correct, but there are others outside your examples and a few at the end
where the data values were beyond those in the table (#N/A). I realise that it probably isn't the final result, but I hope it helps you to get there.

Hercules
 

Attachments

  • Index-Match-help.xlsx
    21.4 KB · Views: 13
Hercules, thank you for your input. That seems to be on the right track. I may end up limiting the data that can be entered into the cell so I only get exact matches. This has been a pain.

Simi
 
Back
Top