Identifying a cell/value in a table

[Ian Murdoch]

Im sure this is simple but can someone tell me how I can identify
cell/value from a simple table ?i.e. table of say location vs equipmen
with the table contents containing values of equipment by specifi
location.

All/any assistance would be gratefully received.

Ian Murdoc

 

[Guest]

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]

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 i
to throw up 7

Rgds

Ia

 

[Guest]

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

 

[Guest]

.... 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))

 

[Ian Murdoch]

Toppers - Brilliant. Thanks very much

Rgds

Ian