X-Y correlations in a database; or two-way lookup

[koledgegirl]

I am struggling even coming up with a correct title for this question..

I have an X-Y database with each combination of X-Y values producing a result
X1 X2 X3
Y1 Z1 Z2 Z3
Y2 Z4 Z5 Z6
Y3 Z7 Z8 Z9

Actually what this is is a Data Table generated through the What-If analysis setup.

I want to create a plot of X and Y correlations resulting in a Z as specified by a constraint.
So what I am doing is
1. Identify the column (X) to lookup value
2. Lookup value (Z) in this column and return it's row
3. Look up Y based on the row number

The down-and-over method.. or something like that...
How in the world do I do this???
I can use Match for step 1, that's easy
I can use vlookup to find the VALUE but not it's row
Then I can't think of a formula to use to return a row of Z without specifying which column Z is located in.

Did I just completely confuse the crap out of everyone?

 

[koledgegirl]

PS. I'm really trying to avoid the INDIRECT function... it's gotta be easier than that??

 

[wigi]

Hi

Assuming headers and data are in A1:D4, A6 contains e.g. X2, A7 contains e.g. Z8, this will work:

=LOOKUP($A$7,INDEX($B$2:$D$4,,MATCH(A6,$B$1:$D$1,0)),$A$2:$A$4)

 

[koledgegirl]

omg..... genious!
thank you!!!
so now one more complication, if the LOOKUP finds and exact match - great; if not - it'll look for the first lesser value. how do i make it took for the first greater value?

 

[wigi]

For instance,

=INDEX($A$2:$A$4,MIN(IF(INDEX($B$2:$D$4,,MATCH(A6,$B$1:$D$1,0))>=A7,ROW(INDEX($B$2:$D$4,,MATCH(A6,$B$1:$D$1,0)))-ROW($A$2)+1,10^10)))

confirmed with Ctrl-Shift-Enter since it is an array formula.

 

[koledgegirl]

you
are
amazing
!

okay. so i'd really try to understand this ... what combination makes excel go through iterative loops to find the closest greater value? the trick's gotta be in the fact that you're not identifying the row for the index... i don't know how to use array formulas at all...
well if this is easy to explain i'd appreciate it, i'll try to read up on it meanwhile