Match Index to an approximate value

[bturner73]

Hi everyone,

I really appreciate any assistance that can provide. I will attach a sample template of the issue that I describe below. I am using the match, index and vlookup formulas together to pull information. The issue with the formula that I have is that if the information found in column B(always a number) is not found I want it to go to the previous value and pull the value found in column C. I feel like I explained that terribly so hopefully the attached spreadsheet will demonstrate my issue more clearly. I have the data in columns A, B and C. The formula found in G3 works but only when the value found in H1 is also matching to the chart data this is formula used in G3: {=INDEX($C$1:$C$24,MATCH(VLOOKUP($F$1,$A$1:$C$24,1,FALSE)&VLOOKUP($H$1,$B$1:$C$24,1,FALSE),$A$1:$A$24&$B$1:$B$24,0))}

To account for wanting to find the approximate value in column b i changed the vlookup for that portion of the formula to TRUE. However, it only works some of the time, and I can't find a rhyme or reason when and why. The formula I tried in G11 is: {=INDEX($C$1:$C$24,MATCH(VLOOKUP($F$1,$A$1:$C$24,1,FALSE)&VLOOKUP($H$1,$B$1:$C$24,1,TRUE),$A$1:$A$24&$B$1:$B$24,0))}. In the case of this formula I would want the value to return $0.98 which is the next lowest value found in column B.

Please let me know if any further clarification is needed.

Thanks again!

[AlKey]

Try this array formula in G11

Please Login or Register  to view this content.

***Array formula must be entered by using key combination of CTRL+SHIFT+ENTER and not just ENTER

[dflak]

What do you mean by "previous value?" Do you mean one shipment number less? Or do you mean the value above it in the table (which doesn't make sense since you can't find it)? Or do you mean the value that is next lowest in line, such as in the example, 14 is not found for 123 company so 9 is the next lowest number in line.

[dflak]

Wait a minute. I think I figured this out.

First you have to find the shipment range that goes with the company name: IF(Table1[Company]=F9,Table1[Shipment],FALSE)

Then we look for the value using VLOOKUP with the True flag (since shipments appear to be ordered ascending) VLOOKUP(H9,IF(Table1[Company]=F9,Table1[Shipment],FALSE),1,TRUE)

This gets us the number or the number above it. So for company 123 and shipment 14, 14 isn’t found so it jumps up one to 10.

Then we need to find out where shipment 10 is (assuming shipment numbers are unique): MATCH(VLOOKUP(H9,IF(Table1[Company]=F9,Table1[Shipment],FALSE),1,TRUE),Table1[Shipment],0)

And finally, we take the index of that: =INDEX(Table1,MATCH(VLOOKUP(H9,IF(Table1[Company]=F9,Table1[Shipment],FALSE),1,TRUE),Table1[Shipment],0),3)

As an array formula, of course.

[bturner73]

Thank you both very much, after working through both formulas they appear to do the same thing just in two slightly different ways (unless I am missing something).

Thank you both once again it is much appreciated!

[AlKey]

Thank you both very much, after working through both formulas they appear to do the same thing just in two slightly different ways (unless I am missing something).

Thank you both once again it is much appreciated!

I think you are missing. Then formula by dflak will fail if you change values in H1. BTW, you don't need two formulas. The one I provided will work with all values.