View Single Post
  #4   Report Post  
Old August 5th 06, 07:48 PM posted to microsoft.public.excel.worksheet.functions
Biff Biff is offline
external usenet poster
First recorded activity by ExcelBanter: Jul 2006
Posts: 1,688
Default Populating a field based on lookup values


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:


"Sav_C" wrote in message

Thanks. This is exactly what I want. However I need it to look up the
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
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 do this and get #VALUE! as the answer. This also happens for the column

Am I being really dumb here?

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

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

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

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

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


"Biff" wrote:


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

Sample file: Index.xls 13.5kb

Enter the number you want in A1.


"Sav_C" wrote in message

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.
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
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
Column it lives in according to the table. i.e. the number 8 would be
Row2, Column1.

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

I appreciate there probably a million ways of doing this and I really
know Excel well enough to know the best one.

Does anyone have any ideas?