excel vlookup and offset

Question

excel vlookup and offset

aresar (Civil/Environmental)

(OP)

3 Mar 04 18:12

I am trying to get data from the vlookup command, but my criteria is very strict as to what I am looking for. I think I need to have the OFFSET function included, but I am not sure how.

Sheet 1

A B C D
2 2 1 (function)
3 3 2
4 4 3

2a 2a 2
2b 2b 2a

7 7 2
8 8 7

Column A or B is the number I want to reference in the formula and associate it with the number in Column C.

Sheet 2

A B C........G

X 2 X.......7700
X 7 X.......8600
X 8 X.......4200

X 1 X.......1000
X 2 X.......1200
X 3 X.......1400
X 4 X.......1100

X 2 X.......2100
X 2A X.......2400
X 2B X.......2200

The data I need to get is in Column G referencing Colum B(columns A & C have no significance)

If I put a function in Sheet 1, D2; I want to find the number from Sheet 2 that will give me the correct number for "2" in Sheet 1, A2 that is associated with the "1" from Sheet 1, C3. The solution it extracts from Sheet 2, Column G is 1200 because the "2" is associated with the number "1" above it.

bltseattle (Civil/Environmental) · Answer 1 · 2004-03-04 18:08:28

Huh?

Very confusing, especially when you say the "2" is associated with the number "1" above it.

Which values when looked up would ever return the value 2100, from your example?

More example calcs, or completing your sheet 1 column D above, would help to clarify what you want the formula to do.

joerd (Chemical) · Answer 2 · 2004-03-05 09:56:58

I agree this is very confusing. Maybe you can take a shot at it yourself, using MATCH, INDEX, VLOOKUP, OFFSET combinations. The people in this forum will probably try to help you out troubleshooting whatever you come up with.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

lilliput1 (Mechanical) · Answer 3 · 2004-03-05 13:16:43

You can't do a vlookup or hlookup if the search field is not in ascending order. The ascending order may not be apparent. You have to use Data Sort & see how Excel sorts it accordingly.

Come Join Us!

Posting Guidelines

excel vlookup and offset