Vlookup question

[freud1]

Hello,

I've made a spreadsheet which relies heavily on the VLOOKUP formula.

I am having trouble with cell BH9, this is supposed to pull the lowest value in area I8:I14 then put the corresponding name in cell BH9.

I am currently using the formula =VLOOKUP(SMALL(I8:I14,1),I8:J14,2,1)

However this is kicking out an error in cell BH9 of #N/A

I don't know what it is as it seems to be working fine for the others.

You may need to unhide the table as there are Vlookup References which are hidden.

If you can resolve the formula or give me a better way of doing this then I would be very greatful!

Thanks

Spreadsheet Attached

[StephenR]

If you change the '1' at the end to 'FALSE' it works. I'm at a loss to explain why this should be the case (edit: when it works for all the others.)

[NBVC]

Change the last argument to 0 in the Vlookup(). This looks for exact match. 1 looks for approximate match....

=VLOOKUP(SMALL(I8:I14,1),I8:J14,2,0)

[NBVC]

If you change the '1' at the end to 'FALSE' it works. I'm at a loss to explain why this should be the case.

If you use 1, your 1st column in the lookup table must be in ascending order for it to work properly... and then it looks for the closest value that is smaller than or equal to the lookup value.

[JBeaucaire]

Just take out the final reference in that cell altogether. It's not needed. In fact none of those cells in that range need the final argument.

Please Login or Register  to view this content.

[Cheeky Charlie]

Actually the default value of the final argument is true - i.e. look for value or next value smaller (assume sorted lookup). So taking the value out is the same as 1 is the same as true - i.e. doesn't work.

Excel "encourages" the user towards sorted arrays and range_lookup = true (which we might perceive as more obfuscated) as the function operates much faster that way (how fast depends very much on the order of the data). I, personally, very rarely find the increase in speed to be worth the ambiguity/fussiness of the function with range_lookup = true, but perhaps I'm lazy...?

CC

[freud1]

thanks guys!

I took the last argument off and it worked a treat!

I love this site!

Much Appreciated!

[NBVC]

thanks guys!

I took the last argument off and it worked a treat!

I love this site!

Much Appreciated!

You took the last argument off and it worked?

Or did you change it to a 0 or FALSE?

[JBeaucaire]

Please Login or Register  to view this content.

All three are equivalent and return the same result - MARK. Any will work.

I used a bad verbiage saying "taking out the final argument", I meant remove the value after the comma or use 0 as NBVC suggested. Removing the comma too turns the default TRUE on and it stops working.

[Cheeky Charlie]

ah, I see... yucky! (in my most polite tone of voice) but absolutely correct. I think my feeble mind couldn't cope with leaving ",)" as false - I thought cunningly using 0 and 1 for booleans was being concise!

CC

[Tirren]

Please Login or Register  to view this content.

All three are equivalent and return the same result - MARK. Any will work.

I used a bad verbiage saying "taking out the final argument", I meant remove the value after the comma or use 0 as NBVC suggested. Removing the comma too turns the default TRUE on and it stops working.

Exactly! Removing the final comma results in the formula evaluating to TRUE or 1, adding it and leaving it blank results in NULL which is the same as 0 which is the same as FALSE.

I almost always use 0 or last comma to explicit an exact match, its too easy to get the "wrong" data by using 1 or TRUE as it doesn't always spit out what you expect, and if you don't know this, you might provide yourself or your client inaccurate data. At least with the FALSE/0 it will result in an actual answer or give you an error letting you know it couldn't find one.

There are situations where using the TRUE value is great though. Best example is if you want the data to lookup something but if it doesn't find it, to return a default value.

Cheers! Glad it worked for you.