Looking for help with the lookup function.
Looking for help with the lookup function.
(OP)
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.
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.
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/
RE: Looking for help with the lookup function.
=LOOKUP(MROUND(A3,50),A2:I2,A1:I1)
RE: Looking for help with the lookup function.
RE: Looking for help with the lookup function.
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/