Hi,

Thanks. This is exactly what I want. However I need it to look up the value

in a different field than the top left corner as you have in your example.

So, I copied the cells onto my spreadsheet, so far so good - I can still

change the value in A1 (now at Y10) and get the correct values out at the

bottom. Obviously the table is at a different position but has been

automatically picked up by Excel.

What I want to do is lookup the value in B2 and put the row value in L2 and

the column value in R2. When I paste the formulae into L2 and R2, by my

reckoning I should only need to change the value of the field to lookup i.e.

B2.

I do this and get #VALUE! as the answer. This also happens for the column

value.

Am I being really dumb here?

I have pasted the formulae below, showing what works and what doesn't.

Working

---------

Row

=IF(COUNTIF(Z11:AF17,Y10),INDEX(Y11:Y17,MAX((Z11:A F17=Y10)*(ROW(Z11:AF17)-ROW(Z11)+1))),"")

Column

=IF(COUNTIF(Z11:AF17,Y10),INDEX(Z10:AF10,MAX((Z11: AF17=Y10)*(COLUMN(Z11:AF17)-COLUMN(Z11)+1))),"")

Not Working

--------------

RowA

=IF(COUNTIF(Z11:AF17,B2),INDEX(Y11:Y17,MAX((Z11:AF 17=B2)*(ROW(Z11:AF17)-ROW(Z11)+1))),"")

ColumnA

=IF(COUNTIF(Z11:AF17,B2),INDEX(Z10:AF10,MAX((Z11:A F17=B2)*(COLUMN(Z11:AF17)-COLUMN(Z11)+1))),"")

Thanks.

"Biff" wrote:

Hi!

Not sure I follow your setup but here is a sample file that does what you

want:

Sample file: Index.xls 13.5kb

http://cjoint.com/?iexXAsqp5e

Enter the number you want in A1.

Biff

"Sav_C" wrote in message

...

Hi,

I have a spreadsheet which has columns with headings A-F. Under these

columns I have some number e.g. 5, 9, 12, 27, 36, 47.

i.e.

A B C D E F

5 9 12 27 36 47

I also have 6 blank columns headed RowA - Row F, plus a further 6 blank

columns called ColA - ColF.

I have a lookup table (7x7 grid) that maps rows and columns that I wish to

use. Basically the grid shows:

Column1 Column2 Column 3.... Column7

Row1 1 2 3... 7

Row2 8 9 10... 14

...

Row7 43... 49

I want to lookup the number displayed in A and find out which Row and

which

Column it lives in according to the table. i.e. the number 8 would be in

Row2, Column1.

I want to put the row result under RowA and the column result under

ColumnA.

I appreciate there probably a million ways of doing this and I really

don't

know Excel well enough to know the best one.

Does anyone have any ideas?

Thanks.