intersection of row and column
 

Hello,

I have the following table on sheet 2
attribute a attribute b attribute 3 attribute 4
tom 55 67 32 77
harry 45 65 34 88
george 22 77 54 32
bill 4 87 45 12

On sheet 1 , I require the unique intersection value for the attrebutes
listed for eg:
look up attribute in row, look up name in coumn and return the intersection
value...

column A column B Column C
attribute a george


Please advice - i am trying to use the index formual but it is not working--

Hi!

Here's one way.

Assume this table is on Sheet2 in the range A1:E5:

attribute a attribute b attribute 3 attribute 4
tom 55 67 32 77
harry 45 65 34 88
george 22 77 54 32
bill 4 87 45 12

On Sheet1 you have:

A1 = attribute A
B1 = George
C1 = formula:

=VLOOKUP(B1,Sheet2!A2:E5,MATCH(A1,Sheet2!A1:E1,0), 0)

Returns 22

Biff

"a" wrote in message
...
Hello,

I have the following table on sheet 2
attribute a attribute b attribute 3 attribute 4
tom 55 67 32 77
harry 45 65 34 88
george 22 77 54 32
bill 4 87 45 12

On sheet 1 , I require the unique intersection value for the attrebutes
listed for eg:
look up attribute in row, look up name in coumn and return the
intersection
value...

column A column B Column C
attribute a george


Please advice - i am trying to use the index formual but it is not
working--

... trying to use the index formula ...

Alternatively, on the same set-up but using INDEX,
you could also try in say, D1:
=INDEX(Sheet2!A1:E5,MATCH(B1,Sheet2!$A$1:$A$5,0),M ATCH(A1,Sheet2!$A$1:$E$1,0
))
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

tHANK YOU MAX AND BIFF
- i was able to resolve the problem with the index solution suggested by
max..and it worked fine...... the vlook formula returned an erroe
message......regards,
"Max" wrote:

... trying to use the index formula ...

Alternatively, on the same set-up but using INDEX,
you could also try in say, D1:
=INDEX(Sheet2!A1:E5,MATCH(B1,Sheet2!$A$1:$A$5,0),M ATCH(A1,Sheet2!$A$1:$E$1,0
))
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

You're welcome. Glad to hear that you got it working.
Biff's suggested formula works just as well (and is shorter!)
Maybe try copy pasting his formula again into C1
- it works fine here.
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"a" wrote in message
...
tHANK YOU MAX AND BIFF
- i was able to resolve the problem with the index solution suggested by
max..and it worked fine...... the vlook formula returned an erroe
message......regards,

With this layout you could also use the Intersection Operator

Select the table.

InsertNameCreate. Top row and Left Column.

Now in an unused cell enter =george attribute_a (note the underscore, if you
had no space you would not require the underscore)


Gord Dibben Excel MVP

On Mon, 18 Apr 2005 22:25:02 -0700, "a" wrote:

Hello,

I have the following table on sheet 2
attribute a attribute b attribute 3 attribute 4
tom 55 67 32 77
harry 45 65 34 88
george 22 77 54 32
bill 4 87 45 12

On sheet 1 , I require the unique intersection value for the attrebutes
listed for eg:
look up attribute in row, look up name in coumn and return the intersection
value...

column A column B Column C
attribute a george


Please advice - i am trying to use the index formual but it is not working--