Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two dimensional lookup
Trying to get value to return from a table where the row and column reference
intersect and to bring that value back to the previous worksheet. EG table looks a litle like this A B C D E F abc1 10 12 14 16 22 57 abc2 20 22 25 29 34 60 abc3 50 67 85 99 105 110 So say I wanted to bring back the value where abc2 and c intersect = 25 How do I do this? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two dimensional lookup
Look at Vlookup and Hlookup in help
-- Martin "CDog" wrote: Trying to get value to return from a table where the row and column reference intersect and to bring that value back to the previous worksheet. EG table looks a litle like this A B C D E F abc1 10 12 14 16 22 57 abc2 20 22 25 29 34 60 abc3 50 67 85 99 105 110 So say I wanted to bring back the value where abc2 and c intersect = 25 How do I do this? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two dimensional lookup
Assuming your data is in A1:G4 and you have the value abc1 in H1 and C
in H2 then try: =INDEX($A$1:$G$4,MATCH(H1,$A$1:$A$4,0),MATCH(H2,$A $1:$G$1,0)) Hope this helps Rowan CDog wrote: Trying to get value to return from a table where the row and column reference intersect and to bring that value back to the previous worksheet. EG table looks a litle like this A B C D E F abc1 10 12 14 16 22 57 abc2 20 22 25 29 34 60 abc3 50 67 85 99 105 110 So say I wanted to bring back the value where abc2 and c intersect = 25 How do I do this? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two dimensional lookup
I need it in one equation the reference in the row (abc2) and the column are
sourced from another sheet as to what they are... Its a little more complex than just using V and H lookup "Martin" wrote: Look at Vlookup and Hlookup in help -- Martin "CDog" wrote: Trying to get value to return from a table where the row and column reference intersect and to bring that value back to the previous worksheet. EG table looks a litle like this A B C D E F abc1 10 12 14 16 22 57 abc2 20 22 25 29 34 60 abc3 50 67 85 99 105 110 So say I wanted to bring back the value where abc2 and c intersect = 25 How do I do this? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two dimensional lookup
Hi Rowan - thanks for that I think I am getting closer - but I suspect it may
be even a little more complex - The row and column values I am trying to match came from another work sheet - so I am ending up with a formula that looks a little like this: =INDEX('Problem Solving Matrix'!$1:$65536,MATCH(M90,'Problem Solving Matrix'!$A$1:$A$362,0),MATCH(I90,'Problem Solving Matrix'!$A$1:$AA$361,0)) Now I know the above formula is not quite right but I cannot see where I am going wrong - the formula gives #N/A error. Any ideas? "Rowan Drummond" wrote: Assuming your data is in A1:G4 and you have the value abc1 in H1 and C in H2 then try: =INDEX($A$1:$G$4,MATCH(H1,$A$1:$A$4,0),MATCH(H2,$A $1:$G$1,0)) Hope this helps Rowan CDog wrote: Trying to get value to return from a table where the row and column reference intersect and to bring that value back to the previous worksheet. EG table looks a litle like this A B C D E F abc1 10 12 14 16 22 57 abc2 20 22 25 29 34 60 abc3 50 67 85 99 105 110 So say I wanted to bring back the value where abc2 and c intersect = 25 How do I do this? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two dimensional lookup
Hi!
=INDEX('Problem Solving Matrix'!$1:$65536,MATCH(M90,'Problem Solving Matrix'!$A$1:$A$362,0),MATCH(I90,'Problem Solving Matrix'!$A$1:$AA$361,0)) A couple of things: It appears that your table is in the range A1:AA362. It's very inefficient to index the entire sheet when you only need to index a much smaller portion. The problem is this portion: MATCH(I90,'Problem Solving Matrix'!$A$1:$AA$361,0) Try changing it to: MATCH(I90,'Problem Solving Matrix'!$A$1:$AA$1,0) And, if row 1 of the table is headers and not really part of the data: MATCH(I90,'Problem Solving Matrix'!$A$1:$AA$1,0)+1 Biff "CDog" wrote in message ... Hi Rowan - thanks for that I think I am getting closer - but I suspect it may be even a little more complex - The row and column values I am trying to match came from another work sheet - so I am ending up with a formula that looks a little like this: =INDEX('Problem Solving Matrix'!$1:$65536,MATCH(M90,'Problem Solving Matrix'!$A$1:$A$362,0),MATCH(I90,'Problem Solving Matrix'!$A$1:$AA$361,0)) Now I know the above formula is not quite right but I cannot see where I am going wrong - the formula gives #N/A error. Any ideas? "Rowan Drummond" wrote: Assuming your data is in A1:G4 and you have the value abc1 in H1 and C in H2 then try: =INDEX($A$1:$G$4,MATCH(H1,$A$1:$A$4,0),MATCH(H2,$A $1:$G$1,0)) Hope this helps Rowan CDog wrote: Trying to get value to return from a table where the row and column reference intersect and to bring that value back to the previous worksheet. EG table looks a litle like this A B C D E F abc1 10 12 14 16 22 57 abc2 20 22 25 29 34 60 abc3 50 67 85 99 105 110 So say I wanted to bring back the value where abc2 and c intersect = 25 How do I do this? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Two dimensional lookup
Thanks Guys - Works a treat now
Cheers CDog! "Biff" wrote: Hi! =INDEX('Problem Solving Matrix'!$1:$65536,MATCH(M90,'Problem Solving Matrix'!$A$1:$A$362,0),MATCH(I90,'Problem Solving Matrix'!$A$1:$AA$361,0)) A couple of things: It appears that your table is in the range A1:AA362. It's very inefficient to index the entire sheet when you only need to index a much smaller portion. The problem is this portion: MATCH(I90,'Problem Solving Matrix'!$A$1:$AA$361,0) Try changing it to: MATCH(I90,'Problem Solving Matrix'!$A$1:$AA$1,0) And, if row 1 of the table is headers and not really part of the data: MATCH(I90,'Problem Solving Matrix'!$A$1:$AA$1,0)+1 Biff "CDog" wrote in message ... Hi Rowan - thanks for that I think I am getting closer - but I suspect it may be even a little more complex - The row and column values I am trying to match came from another work sheet - so I am ending up with a formula that looks a little like this: =INDEX('Problem Solving Matrix'!$1:$65536,MATCH(M90,'Problem Solving Matrix'!$A$1:$A$362,0),MATCH(I90,'Problem Solving Matrix'!$A$1:$AA$361,0)) Now I know the above formula is not quite right but I cannot see where I am going wrong - the formula gives #N/A error. Any ideas? "Rowan Drummond" wrote: Assuming your data is in A1:G4 and you have the value abc1 in H1 and C in H2 then try: =INDEX($A$1:$G$4,MATCH(H1,$A$1:$A$4,0),MATCH(H2,$A $1:$G$1,0)) Hope this helps Rowan CDog wrote: Trying to get value to return from a table where the row and column reference intersect and to bring that value back to the previous worksheet. EG table looks a litle like this A B C D E F abc1 10 12 14 16 22 57 abc2 20 22 25 29 34 60 abc3 50 67 85 99 105 110 So say I wanted to bring back the value where abc2 and c intersect = 25 How do I do this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Another way to lookup data | Excel Worksheet Functions | |||
Lookup function help | Excel Worksheet Functions | |||
Lookup Vector > Lookup Value | Excel Worksheet Functions | |||
Lookup function w/Text and Year | Excel Worksheet Functions | |||
double lookup, nest, or macro? | Excel Worksheet Functions |