Find a cell in a chart?

[JoeBed]

I have a chart with the top row A1:AJ1 numbers 1 thru 10. On the left side Column A1:A10 are also a set of numbers1 thru 10. The cells down and across are the numbers I need to look up. If cell A25 shows a 2 and cell a
A26 shows a 4. How can I right a formula that will find the correct cell on the top row to match A25 (2) and the correct cell on the left column A26 (4) and show the number from the chart where the column and the row intersect in A27?
This is the third time I have asked this question, mayby third times a charm. Thanks for looking and many thanks if you can answer it.
JoeBed

[swatsp0p]

Joe, when you say 'Chart', do you mean Table? A chart is Excel's name for a graph, such as a bar chart, pie chart, etc.

A 'Table' is data laid out in rows and columns of a spread sheet. It sound to me that you have a Table of data. You list 36 columns (A:AJ) and 10 rows (A1:A10) of data in your table. Do you really mean 10 columns (A:J) numbered in row 1 from 1-10 and 10 rows from A1:A10 numbered 1-10?

If this is your layout, then, of course, the references in A25 (column) and A26 (row) could never be a 1. With that said, to find cell 2,4 (which means 2nd column[B] and 4th row use this formula in A27:

=INDIRECT("R"&$A$26&"C"&$A$25,FALSE)

I'm guessing this is what you are looking for.

whew...

Bruce

[JoeBed]

Thanks Bruce. You are a good guesser. I have trouble puttung what is in my head into print. This help will get me where I want to go. Thanks again.
Joe

[swatsp0p]

Thanks for the feedback, Joe. This was an interesting puzzle to figure out!

Cheers!

Bruce

[JoeBed]

Bruce, I am still having trouble figuring this out. I am going to try a new way to expain what I am after.

.....A.....B.....C.....D.....E.....F.....G.....H.....I
1..........10...20....30...40...50
2..31....1.....2......3.....4......5....30...51....?
3..41...........6......7.....8......9
4..51..................10....11...12
5..61..........................13...14
6..71.................................15

The data table is Rows 1 thru 6 and Columns A thru F. The dots are to keep this looking like a excell spreadsheet.
In cell G2 I would have entered a number that I know will match one of the top numbers in the table row 1 (10,20,30,40,50).
In cell H2 I would have entered a number that I know will match one of the Left side numbers in the column (31,41,51,61,71)
The answer I need to come up with is 10 (the intersection of 30 and 51 in cell I2).

[swatsp0p]

OK, we just need to identify the Row and Column of each value in G2 and H2 and use that with the INDIRECT function. We can use MATCH to accomplish this.

=MATCH(H2,A2:A6,0)+1 will find the row number of the value listed in H2 (because the data starts in row 2, we need to add '1' to the result to identify the row number) In this case, 51 is the third value in the list, adding 1 gives us 4 (row 4).

=MATCH(G2,B1:F1,0)+1 will find the column number of the G2 value. Same as above, because we start in the 2nd column, we need to add 1. Here, 30 is again the third value in the list, adding 1 gives us 4.

Now, we combine all of this in the INDIRECT function as such:

=INDIRECT("R"&(MATCH(H2,A2:A6,0)+1)&"C"&(MATCH(G2,B1:F1,0)+1),FALSE)

This looks at "R"ow 4 and "C"olumn 4 (D4) which in this case returns 10.

Good Luck

Bruce