Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Identifying a cell/value in a table
Im sure this is simple but can someone tell me how I can identify a cell/value from a simple table ?i.e. table of say location vs equipment with the table contents containing values of equipment by specific location. All/any assistance would be gratefully received. Ian Murdoch -- Ian Murdoch ------------------------------------------------------------------------ Ian Murdoch's Profile: http://www.excelforum.com/member.php...fo&userid=8441 View this thread: http://www.excelforum.com/showthread...hreadid=566581 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Identifying a cell/value in a table
Ian,
INDEX appears to be a likely candidate. If your table contains Location (Col A), Equipment (Col B) and Value (Col C) then to get the value of a piece of equipment for a given location: =INDEX(C:C.match(1,(A:A=Location)*(B:B=Equipment), 0),1) entered with Ctrl+Shift+Enter (as an array formula) which appear with {} around the formula. "Location" is the location and "Equipment" the equipment. Both these could be cells (e.g. X1,X2)so the above formula would become: =INDEX(C:C.match(1,(A:A=X1)*(B:B=X2),0),1) Also look at VLOOKUP in HELP as another possibility HTH "Ian Murdoch" wrote: Im sure this is simple but can someone tell me how I can identify a cell/value from a simple table ?i.e. table of say location vs equipment with the table contents containing values of equipment by specific location. All/any assistance would be gratefully received. Ian Murdoch -- Ian Murdoch ------------------------------------------------------------------------ Ian Murdoch's Profile: http://www.excelforum.com/member.php...fo&userid=8441 View this thread: http://www.excelforum.com/showthread...hreadid=566581 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Identifying a cell/value in a table
Thanks Toppers, but not sure how it works with say : LOCATION EQUIPMENT a b c d e L 1 3 1 4 9 M 6 1 7 20 2 N 7 8 7 8 4 O 14 1 5 6 7 P 6 2 1 516 56 Q 14 34 54 76 77 so by identifying location say, c and equipment say, n, how do I get it to throw up 7 Rgds Ian -- Ian Murdoch ------------------------------------------------------------------------ Ian Murdoch's Profile: http://www.excelforum.com/member.php...fo&userid=8441 View this thread: http://www.excelforum.com/showthread...hreadid=566581 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Identifying a cell/value in a table
Ian,
Still INDEX but I misinterpretted your table (my fault!). Assuming your table is in range A1:F8 use the following: =INDEX($B$3:$F$8,MATCH("c",$B$2:$F$2,0),MATCH("N", $A$3:$A$8,0)) $B$3:$F$8 is (array) of the costs $B$2:$F$2 are the locations $A$3:$A$8 are the locations The MATCH functions find the row and column intersetctions to give the location in array $B$3:$F$8 As before you can assign "c" and "N" to cells and replace these by cells in the formula. HTH "Ian Murdoch" wrote: Thanks Toppers, but not sure how it works with say : LOCATION EQUIPMENT a b c d e L 1 3 1 4 9 M 6 1 7 20 2 N 7 8 7 8 4 O 14 1 5 6 7 P 6 2 1 516 56 Q 14 34 54 76 77 so by identifying location say, c and equipment say, n, how do I get it to throw up 7 Rgds Ian -- Ian Murdoch ------------------------------------------------------------------------ Ian Murdoch's Profile: http://www.excelforum.com/member.php...fo&userid=8441 View this thread: http://www.excelforum.com/showthread...hreadid=566581 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Identifying a cell/value in a table
.... got my rows and colums wrong way round ... sorry!
=INDEX($B$3:$F$8,MATCH("Q",$A$3:$A$8,0),MATCH("c", $B$2:$F$2,0)) "Toppers" wrote: Ian, Still INDEX but I misinterpretted your table (my fault!). Assuming your table is in range A1:F8 use the following: =INDEX($B$3:$F$8,MATCH("c",$B$2:$F$2,0),MATCH("N", $A$3:$A$8,0)) $B$3:$F$8 is (array) of the costs $B$2:$F$2 are the locations $A$3:$A$8 are the locations The MATCH functions find the row and column intersetctions to give the location in array $B$3:$F$8 As before you can assign "c" and "N" to cells and replace these by cells in the formula. HTH "Ian Murdoch" wrote: Thanks Toppers, but not sure how it works with say : LOCATION EQUIPMENT a b c d e L 1 3 1 4 9 M 6 1 7 20 2 N 7 8 7 8 4 O 14 1 5 6 7 P 6 2 1 516 56 Q 14 34 54 76 77 so by identifying location say, c and equipment say, n, how do I get it to throw up 7 Rgds Ian -- Ian Murdoch ------------------------------------------------------------------------ Ian Murdoch's Profile: http://www.excelforum.com/member.php...fo&userid=8441 View this thread: http://www.excelforum.com/showthread...hreadid=566581 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Identifying a cell/value in a table
Toppers - Brilliant. Thanks very much Rgds Ian -- Ian Murdoch ------------------------------------------------------------------------ Ian Murdoch's Profile: http://www.excelforum.com/member.php...fo&userid=8441 View this thread: http://www.excelforum.com/showthread...hreadid=566581 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table external XLS file source change and GETPIVOTDATA refresh | Excel Discussion (Misc queries) | |||
Derived Columns in Pivot Table | Excel Discussion (Misc queries) | |||
How to import a table on a Web page to Excel? | Excel Discussion (Misc queries) | |||
Change Data In Pivot Table | New Users to Excel | |||
Pivot Table - identifying datasource | Excel Discussion (Misc queries) |