Identifying a cell/value in a table

[Ian Murdoch]

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

[Toppers]

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

[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 it to throw up 7

Rgds

Ian

[Toppers]

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

[Toppers]

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

[Ian Murdoch]

Toppers - Brilliant. Thanks very much

Rgds

Ian