Excel novice pulling my hair out-Vlookup doing random math & can’t find external links

Topic

New Reply

I am an Excel novice and new to the Vlookup function. It seems pretty straight forward but I have been trying to figure out why it has not been working for me for 2 days. On the HVoipQuote sheet, cell C24 should return a price based on the value entered in cell A24. The price is looked up on sheet1, table array is Sheet1A2:B23. Th lookup has been returning random values that are not even in the table, seems like there is some mysterious math going on. I am hoping that there is something silly going on but would love some assistance from someone that has more experience with xls. Any help is appreciated

Reply | Quote

Replies

Hi

Welcome to the Lounge as a new poster!

..see attached file.
I just named the lookup block as handsetBlock
For an exact match lookup, you need to include FALSE in the formula:
=IFERROR(VLOOKUP(A24,handsetBlock,2,FALSE),0)

zeddy

Reply | Quote

AWESOME, thank you – very much. So instead of naming the table array you can just “define name” similar to setting up a drop down list?

Reply | Quote

Hi Fishunt

Welcome to the world of the balding.

A bit of background on Vlookup()
By design only 3 of the 4 arguments :- target, Lookup table, and column position of desired data are required.

By design and default Vlookup() will return a match based on the value that is either equal to target OR nearest to and smaller than target.

When you append the fourth argument and specify “False” you are instructing the function to force an exact match for the target data. .. instead of nearest to or smaller than target.

By default and design the function expects that column 1 of the lookup table to be sorted in ascending order.
This is easy to spot with numeric data but a tad more tricky with alphanumerics. ( “a1” is not the same as “a 1”)

Use of alphanumeric data in column 1 of the lookup table is an additional reason to specify the “false” in the 4th argument.

The inclusion of “False” as argument 4 solved your problem as it negated the default behaviour of the vlookup().

On a copy I would suggest observing the effects of sorting your lookup table by col 1 just for your education.

Zeddy makes a good point by naming the block. It makes the formula far easier to read.

Have a look at the MS support site for more information.
Hope this helps in the future.

G

Reply | Quote