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.


Looking for help with the lookup function.

Looking for help with the lookup function.

I am having trouble with the lookup function and hope someone can help me. A simplified Excel 207 spreadsheet:
red blue green yellow purple orange mauve pink black
150 200 250 300 350 400 450 500 550
Input cell(A3) Result cell B3
299.9 =LOOKUP(A3,A2:I2,A1:I1) returns green. 300 to 349.9 returns yellow

Can I have it return "closest to"? I.E. any value between 225 and 249 would return green.

When I try and use =HLOOKUP(A3,A2:I2,A1:I1)I get an #REF! error, why?

My understanding is that the HLOOKUP function compares the input value against the table array (left to right) and stops at the first instant the array number is larger than and displays the corresponding value in that column. Which, is what seems to be happening with my LOOKUP currently, what is the difference?

Thank you for your time.

RE: Looking for help with the lookup function.

There is no built-in option to find the closest match, but if you enter another row with the average of the value in the column and the column to the left, and use that as the lookup range, then it will do what you want. The first column could have half of the first value, or whatever you want the minimum allowable value to be.

HLookup provides similar functionality to Lookup, but requires different input. The lookup values need to be at the top of the table (or in the first column for VLookup), and you select the entire table as the second argument. The required third argument is the row index number of the required output, relative to the top of the table. In your case if you move the colour row to row 3 the correct input would be: =HLOOKUP(A3,B2:I3,2).

In general, if you click on the function symbol to the left of the formula bar you will get the "Insert Function Wizard" which tells you what input is required for each function argument.

In XL2010 and later you also get input prompts as you are entering the function, and in the Insert Function dialog box you can click on F1 to get help related to the specific function, rather than having to plough through the help index to find what you want. It's worth upgrading for this feature alone.

Doug Jenkins
Interactive Design Services

RE: Looking for help with the lookup function.

This should work in your original example:

RE: Looking for help with the lookup function.

Thank you, I will play with that and see if it works better for me. Now that I know where I was going wrong with HLOOkUP I think I have most of my problem solved.

RE: Looking for help with the lookup function.


This should work in your original example:

Very neat.

The only drawback I can see is that the values must have a constant increment.

Doug Jenkins
Interactive Design Services

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!


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