Select cell from table based on values in row 1, and column A

Hello - new member here.., with a question about selecting cells from a table.

I have a workbook with two sheets.

In sheet 1, I make calculations that result in two numbers (integers): call them X, and Y

there are many values of X, but only one value of Y

In sheet 2, I have a table.

Across the top, in Row 1, are integers corresponding to X, that increase from left to right, but don't begin with any fixed number, and don't increase with any fixed step value

down the left side, in column A, are integers that correspond to values of Y, but decrease from top to bottom, and again don't begin with any fixed value, and don't decrease at any fixed step value

in this table, on sheet 2, the rest of the values are text strings - from column B rightward, and from Row 2 downward. A1 is empty

So, what I want to do, is this: for every value of X and the single value of Y obtained in sheet 1, i want to scan row 1 of sheet 2, and find the value of X in row 1 that is closest to each X from sheet 1, and to scan column A of sheet 2, and find the value of Y that is closest to the value of Y obtained in sheet 1.

then for each row 1, column A pair, i want to find the text string in the cell for that row/column intersection and put it in a cell on sheet1.

sorry if i was not clear...

also, I did do a search of the forum and other excel help sites, but mostly found about selecting cell values based on the "indexes", not a row and column.

thanks for any help.

jgf310

and column A of sheet 2, and find the row with the va

Hi,

I think you are looking for an INDEX MATCH MATCH formula.

This type of formula is an array formula. That is entered with CTRL/SHIFT/ENTER.

The INDEX identifies all the possible returns from your array.
The 1st MATCH is for the row.
The 2nd MATCH is for the column.

Attached is a mock up file to show how it works.

Hope this makes sense and is helpful.

Cheers

Thank you southward - certainly, that is very close to what i want to do

but I would need it to return either IMHQ (F4), or QGIF (F5), rather than ADDC (E4)

the input value for selecting the column, was 13.., and the two closest column indexes are 11 (E) and 14 (F) - 13 is closer to 14 than it is to 11, so I want it to choose 14, rather than 11

with respect to the rows, the input value was 25, and the two closest row indexes are 26 (4) and 24 (5) - it chose 26 (4)

i haven't specified what i want to do when the calculated value falls exactly half way between the two index values - I would like to be able to do so, although it's not make or break for my application.

is there anyway to make it do what i want?

thanks again

Here is a version of the formula that will select the closest value:

Please Login or Register  to view this content.

In this version I have the Match type set to "0".

So when the number is exactly in between, the 1st Match (row) will select the next higher, the 2nd Match (column) will select the next lower.

The Match Type (-1, 0, 1) is supposed to determine which direction the MATCH will look. However, with the "MIN(ABS" modification used to find the nearest, the Match Type isn't having the desired effect.

A Match Type of (1) in either of the MATCH's is causing a #N/A.

Maybe there is a Guru out there that can provide a solution that will allow you to select higher or lower if the value is in between.

Hope this makes sense and is helpful.

Cheers

thanks - i will try it later today

with respect to the issue of being exactly half way between - i may be able to force it the way i want it by fudging the value on the first sheet: if the calculated value comes out to be 13.5.., maybe i can use an if/then to make it either 13.4 or 13.6, depending on what i want..

if 13.5 turns out to be exactly half way between, it will go the way i want.., if it isn't half way between.., it won't make a difference - i think...