• 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 does not return expected values.

Dear all,

I have the following Excel sheet.

In sheet1:C3:C10 I have the following formula

Code:
=VLOOKUP(B3;Sheet2!$B$2:$C$10;2;TRUE)

For some reason, however, the sheet returns strange values. Any thoughts on what goes wrong?


Image 0801 190829.png
 

Attachments

  • sales_invoices_incasso_sample.xlsx
    13.8 KB · Views: 5
what results are you expecting - your looking up text , and finding nearest value with a TRUE , so text may return unexpected results because of the assignment excel gives to text

try
=VLOOKUP(B3,Sheet2!$B$2:$C$10,2,FALSE)
so you are looking up an exact value

you are including the titles on sheet 2 , so they are also looked up nearest value - i cant remember how excel assigns text order
 
Dear all,

I have the following Excel sheet.

In sheet1:C3:C10 I have the following formula

Code:
=VLOOKUP(B3;Sheet2!$B$2:$C$10;2;TRUE)

For some reason, however, the sheet returns strange values. Any thoughts on what goes wrong?


View attachment 84847
Hi,
On your VLOOKUP formula, you've used the fourth argument, TRUE which stands for Approximate Match. Well, if your data isn't sorted you won't get your expected results. So, better you look for the exact match. So use FALSE instead of TRUE.
Here's the updated formula:

=VLOOKUP(B3;Sheet2!$B$2:$C$10;2;FALSE)

You can read 8 VLOOKUP Limitations in Excel to avoid this type of problem in the future.

Thanks!
 
Back
Top