# Thread: Index match lookup problems

1. ## Index match lookup problems

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.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

2. 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

3. 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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•