Populating a field based on lookup values

[Sav_C]

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.

 

[Biff]

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]

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: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]

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:AF17=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]

Biff,

It works great. I'll study the website. Thanks for your help. I would never
have known about the Ctrl-Shift-Enter thing.

Excellent help.

 

[Biff]

You're welcome. Thanks for the feedback!

Biff

Biff,

It works great. I'll study the website. Thanks for your help. I would
never
have known about the Ctrl-Shift-Enter thing.

Excellent help.