Populate cell with data based on Table

[PVSD_MSantiago]

[tt]Hello All!

This has probably been answered but I can't seem to find it. I have a workbook with two spreadsheets:

"Tab1" is a table of values in a grid:

A B C D E
1 AB BC CD DE
2 AB 1.10 1.20 1.30 1.40
3 BC 1.50 1.60 1.70 1.80
4 CD 1.90 2.00 2.10 2.20
5 DE 2.30 2.40 2.50 2.60

Column A lists a START and Row 1 lists an END. Data in between is a static number. It's showing sequentially but could be any number

"Tab2" is a user entered form with columns:
A B C
1 START END NUMBER
2 XX XX X.XX
3 XX XX X.XX
4 XX XX X.XX

What I'm looking for is to be able to manually enter start and end values into Tab2 and have the NUMBER auto-populate from the grid in Tab1 like this:

A B C
1 START END NUMBER
2 AB AB 1.10
3 CD BC 2.00
4 BC DE 1.80

I guess I need to know the syntax to say "If Start = Tab1-Column A and End = Tab1-Row 1, put the value where it intersects in this cell". I'm thinking it's some kind of LOOKUP but am not good enough with Excel to get it working. I hope I put enough info in here. Thanks for any assistance!
Michael[/tt]

 

[Andrzejek]

This place looks very close to what you are trying to do: Two-way lookup with INDEX and MATCH
And here is another place for formulas for two-way lookup

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[SkipVought]

Hi,

Here's my solution using OFFSET(), INDEX() & MATCH().

J2: =INDEX(OFFSET($A$1,MATCH($H2,$A$2:$A$5,0),1,1,4),MATCH($I2,$B$1:$E$1,0))

To make the formula more general, assuming that no other data will be on the sheet with your matrix, the formula in J2 ought to be...

J2: =INDEX(OFFSET($A$1,MATCH($H2,$A:$A,0)-1,1,1,COUNTA($1:$1)),MATCH($I2,$1:$1,0)-1)


Skip,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!