Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old August 4th 06, 05:33 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2006
Posts: 1
Default 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   Report Post  
Old August 4th 06, 10:56 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 1,688
Default 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   Report Post  
Old August 5th 06, 02:19 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2006
Posts: 4
Default 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   Report Post  
Old August 5th 06, 07:48 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 1,688
Default 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   Report Post  
Old August 6th 06, 12:22 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2006
Posts: 4
Default 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   Report Post  
Old August 6th 06, 02:49 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 1,688
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculated Field in Pivot Table Based on Two Counted Fields cmlits Excel Discussion (Misc queries) 1 March 30th 06 05:44 AM
Populating a table based on values in another table Bri Excel Worksheet Functions 0 January 26th 06 01:23 AM
lookup serch term and return sum of all values soilcon1 Excel Worksheet Functions 2 December 22nd 05 12:03 AM
How to change drop down values based on another cell value? puneetarora_12 Excel Discussion (Misc queries) 3 October 5th 05 11:28 AM
Selecting data from a list based on entered values GrantM Excel Discussion (Misc queries) 1 December 20th 04 10:59 AM


All times are GMT +1. The time now is 05:03 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017