Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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-- |
#2
|
|||
|
|||
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-- |
#3
|
|||
|
|||
... 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 ---- |
#4
|
|||
|
|||
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 ---- |
#5
|
|||
|
|||
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, |
#6
|
|||
|
|||
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-- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|