| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Populating a field based on lookup values
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Populating a field based on lookup values
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Populating a field based on lookup values
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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Populating a field based on lookup values
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" wrote: 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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Populating a field based on lookup values
You're welcome. Thanks for the feedback!
Biff "Sav_C" wrote in message ... 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" wrote: 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Calculated Field in Pivot Table Based on Two Counted Fields | Excel Discussion (Misc queries) | |||
| Populating a table based on values in another table | Excel Worksheet Functions | |||
| lookup serch term and return sum of all values | Excel Worksheet Functions | |||
| How to change drop down values based on another cell value? | Excel Discussion (Misc queries) | |||
| Selecting data from a list based on entered values | Excel Discussion (Misc queries) | |||
Linear Mode
