Looking for help with the lookup function. 1

[Ralph2]

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.

 

[IDS]

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

http://newtonexcelbach.wordpress.com/

 

[TerryScan]

This should work in your original example:
=LOOKUP(MROUND(A3,50),A2:I2,A1:I1)

 

[Ralph2]

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.

 

[IDS]

This should work in your original example:
=LOOKUP(MROUND(A3,50),A2:I2,A1:I1)

Very neat.

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

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/