How can I do this ??

[Eric_in_EVV]

I have a spreadsheet (call it a matrix) that has item numbers going down
column A and item numbers going across row 1. I need to find the
intersection of any two item numbers based upon item numbers in a different
spreadsheet (call it a plan) in consecutive rows. Let me illustrate:

The Matrix:

Row 1 -> item1 item2 item3
Col A ------ ------- -------
------
item1 1.0 1.5 1.25
item2 1.5 1.0 2.0
item3 1.25 2.0 1.0

The Plan:

row 1-> item1 and a bunch of other stuff in other columns
row 2-> item3 and a bunch of other sutff in other columns

What I need to be able to do is use the item numbers from row 1 and row 2 to
get the value from the Matrix....the value is where item1 and item3 intersect
in the Matrix. So for this example, the value returned would be 1.25.

Does this make any sense.....and is it possible ??

 

[T. Valko]

row 1-> item1

row 2-> item3
the value returned would be 1.25.

So, row 1 is the vertical lookup and row 2 is the horizontal lookup.

One way...

=VLOOKUP(A1,Matrix!A14,MATCH(A2,Matrix!A11,0),0)

 

[Max]

Another way, via index/match
Assume your reference table in A14
where "data" area is in B24

In A12: item1 (the item to match in the vertical col range A2:A4)
In A13: item2 (the item to match in the horiz row range B11)
Then in say, A14:
=INDEX($B$2:$D$4,MATCH(A12,$A$2:$A$4,0),MATCH(A13,$B$1:$D$1,0))
Above is a common, simnple way to read tables & extract intersection values.
Learn it once, apply everywhere else forever. Success? hit the YES below.

 

[Eric_in_EVV]

One more little twist to this tale....

the Matrix I am searching is located in a different file (I know - not a big
deal) and in a tab that is named exactly like the value in column E of the
"plan" as I called ie below. I know there's someway I can reference the tab
name in the formula by using the INDIRECT function, but can't seem to get it
to work when I also have to spell out the file path and name of the
"matrix".....any clues to help me with that ??

Thanks !

 

[Max]

One more little twist to this tale....
Twist it all you want, but always start a new thread for each twist. Btw,
INDIRECT doesn't work with closed source files, which is what you are trying
to do. Once you start talking about filepaths, it infers you're trying to
pull something from closed source files. INDIRECT will work only with
simultaneously open source-to-destination files.

 

[Max]

One more thing, do not keep using the same subject title for every new query
or twist that you want to ask. And your subject line should reflect the
issue at hand in a succinct manner, avoid generics like: How can I do this
??, How can I do that ??, etc