Ok.....

In the sample file, select either one of the formula cells, A11 or B11.

Now, look at the formula as it appears in the formula bar. You'll notice

that the formula is enclosed in squiggly braces: { }. These braces mean the

formula is an array formula. An array formula is different from a normal

formula. When you type a normal formula you hit the ENTER key to place the

formula in a cell. With an array formula it's different. To place an array

formula in a cell you MUST use a combination of keys. Those keys are

CTRL,SHIFT,ENTER. That is, type the formula then hold down both the CTRL key

and the SHIFT key then hit ENTER. If done properly Excel will enclose the

formula in those squiggly braces. You can't just type these braces in. You

MUST use the key combination. Also, when you edit or change an array formula

it MUST be re-entered as an array using the key combination.

So, when you changed the lookup reference to B2 you edited the formula:

=IF(COUNTIF(Z11:AF17,B2),INDEX(Y11:Y17,MAX((Z11:AF 17=B2)*(ROW(Z11:AF17)-ROW(Z11)+1))),"")
You probably didn't re-enter the formula as an array.

So, select the cell with the formula.

Hit function key F2.

That will put you in Edit mode.

Now, hold down both the CTRL key and the SHIFT key then hit ENTER.

More on array formulas:

http://cpearson.com/excel/array.htm
Biff

"Sav_C" wrote in message

...

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.