Re: Populating a field based on lookup values

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 spread***, 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:AF17=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:AF17=B2)*(ROW(Z11:AF17)-ROW(Z11)+1))),"")

ColumnA
=IF(COUNTIF(Z11:AF17,B2),INDEX(Z10:AF10,MAX((Z11:AF17=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" <Sav_C@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F31030D8-0ADA-4903-A350-F352951424D1@xxxxxxxxxxxxxxxx
Hi,

I have a spread*** 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.



.