Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

excel vlookup and offset

excel vlookup and offset

excel vlookup and offset

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

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.

RE: excel vlookup and offset


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.

RE: excel vlookup and offset

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.


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

RE: excel vlookup and offset

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close