INDEX and MATCH help

[Engineering_Intern]

Hello everyone, I am having a little trouble getting the INDEX and MATCH functions to work together. I am using an input in cell B6 that is going to reference an array in G1:G10. However I want it to choose the highest number in the array and VLOOKUP is giving me the smaller of the two numbers. So I decided to use the INDEX and MATCH functions. It works when I input

=INDEX(G1:G10,MATCH(B6,G1:G10,1)) But this gives me the lower number. When I input

=INDEX(G1:G10,MATCH(B6,G1:G10,-1)) it gives me #N/A

Here is the column of numbers I am using.

0.2500
0.3125
0.3750
0.4375
0.5000
0.6250
0.7500
0.8750
1.0000
1.2500


If anyone could point out my error I would be very appreciative.

[oeldere]

You get better help if you post an Excel-example of your workbook, without confidential information.
Make sure the workbook demonstrates your desired results if possible, or just highlight the cells you're trying to fix.
Use BEFORE/AFTER sheets if that helps make it clearer.

[Engineering_Intern]

I am attaching a copy of the Sheet I am working on.

[oeldere]

Maybe like this (see the yellow cell).

Please reply if this is what youre up to.

[singerj6]

I'm not quite sure what you're trying to achieve, the highest number in your array is 1.2500 regardless of what your match criteria is. Usually INDEX and MATCH is used to facilitate a look up in a multiple column array, can you explain your problem further?

[Engineering_Intern]

I'm not quite sure what you're trying to achieve, the highest number in your array is 1.2500 regardless of what your match criteria is. Usually INDEX and MATCH is used to facilitate a look up in a multiple column array, can you explain your problem further?

I am calculating the thickness of a plate and for engineering purposes we would need to select the next highest available thickness. So I have a calculated thickness of 0.347, so I need to choose the 3/8" thick plate not the 5/16". My problem is when I use VLOOKUP it gives me the 5/16" value instead of the 3/8" when I use the approximation match.

---------- Post added at 09:14 AM ---------- Previous post was at 09:09 AM ----------

Maybe like this (see the yellow cell).

Please reply if this is what youre up to.

Yes that seems to have solved my problem. What is the purpose of the additional "+1"?

[oeldere]

match(B6,G1:G10,1) finds the row

You want 1 row below the given answer (above)

So you need to count +1 to get what you want to achieve.