• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VLookUp function doesn't refresh

G

GN0001

Member
Hello Team,

I am using VLookUp function and each time I have to change the source data.
but when I plug in the new data (copy the new data into the sheet from which VLOOKUP function brings back the value), the VLookUp doesn't show the result and I have to re-enter the funtion to be able to pick up the data . What is solution?

Regards,
GN0001
 
Hi ,

Can you post the VLOOKUP formula which you have used ?

I tried a VLOOKUP formula , and it does not give any problem when I paste data into the lookup range.

Narayan
 
I think, calculation mode set to manual, please check calculation mode by clicking formulas tab and then calculation options, then automatic (if not ticked)
 
NARAYANK991 said:
Hi ,

Can you post the VLOOKUP formula which you have used ?

I tried a VLOOKUP formula , and it does not give any problem when I paste data into the lookup range.

Narayan
Click to expand...
Hello Narayan,
It worked today,
It was:
=VLookUP($A$259, $A$12:$A40,13)
 
Hi ,

How did it work today ?

First , the 13 signifies the 13th column in the lookup range , where column 1 refers to the column in which you are looking up ; thus in your case , you are looking up the value in A259 within the range A12:A40 , which means column 1 is column A ; the 13th column will refer to column M , which unfortunately is not in your lookup range ; the formula should not work , unless you change it to :

=VLOOKUP($A$259, $A$12:$M40,13)

Of course , you can extend the lookup range beyond 13 columns , and make it :

=VLOOKUP($A$259, $A$12:$Z40,13)

but the lookup range cannot be less than 13 columns.

Secondly , you must be aware that by omitting the last parameter , which is either TRUE or FALSE ( 1 or 0 ) , you are actually performing an exact or approximate match , for which your range should be sorted on column A , as otherwise the result will be wrong.

If you are looking for an exact match , irrespective of whether your lookup range is sorted or not , you should specify the last parameter as FALSE or 0. Thus , the correct formula would be :

=VLOOKUP($A$259, $A$12:$M40,13,FALSE)

Narayan
 
NARAYANK991 said:
Hi ,

How did it work today ?

First , the 13 signifies the 13th column in the lookup range , where column 1 refers to the column in which you are looking up ; thus in your case , you are looking up the value in A259 within the range A12:A40 , which means column 1 is column A ; the 13th column will refer to column M , which unfortunately is not in your lookup range ; the formula should not work , unless you change it to :

=VLOOKUP($A$259, $A$12:$M40,13)

Of course , you can extend the lookup range beyond 13 columns , and make it :

=VLOOKUP($A$259, $A$12:$Z40,13)

but the lookup range cannot be less than 13 columns.

Secondly , you must be aware that by omitting the last parameter , which is either TRUE or FALSE ( 1 or 0 ) , you are actually performing an exact or approximate match , for which your range should be sorted on column A , as otherwise the result will be wrong.

If you are looking for an exact match , irrespective of whether your lookup range is sorted or not , you should specify the last parameter as FALSE or 0. Thus , the correct formula would be :

=VLOOKUP($A$259, $A$12:$M40,13,FALSE)

Narayan
Click to expand...

Hello Narayan,
I left the false or True part of the function here, and I what I select is in range, and my function worked today. I appreciate for mentioning to me though.
I don't sort my range and I always set the function to False. This shouldn't bring back the wrong result, right?
Many, many thanks,
GN0001
 
Hi ,

Setting the last parameter to FALSE will either return an exact match if there is one , or will return a #N/A error value if there is no match. So it is always OK to use FALSE ; sometimes you may want to use TRUE if your lookup range is sorted , and you are looking for the nearest value in case there is no match.

Narayan
 
Dear GN0001,
You should use structured ref. in base data by making table data. The problem is not as typical as we think so. in vlookup use e.g:
=VLOOKUP(D2,Sales[Date],1,0)

I think it will work.
 
You must log in or register to reply here.
Back
Top