vlookup N/A value

[Mike Huntswarm]

Hi All

I am in need of some friendly support with the vlookup function

below is the formula I have written to pull the data I am looking for
Capture1.JPG

I believe the error is a formatting error of the data which I do not know how to fix
The formula is in number format
The column the data is looking at is in General Format
The sheet the formula table is looking through is in General Format..

Vlookup has always given me so many headaches

thank you in advance for your assistance

[BMV]

Check sheet1!A:A and H:H for text or numeric values.

[AliGW]

Mike - please update your user profile with the version of Excel that you are using. Thanks.

[FDibbins]

Mike, vlookup is a very basic, simple formula, and is 1 of the standard tools for those starting out in excel. However, like many tools, it needs to be used correctly, and with the correct data. Below are a few common errors users make when using vlookup (as well as many other functions)...

1. vlookup works by finding something (criteria), then returning something else related to that (pretty straight forward). BUT, there are rules that need to be followed
- general syntax =vlookup(what-to-find, data-range-containing-result, column-number-in-range-containing-answer, TRUE/1 (for sorted data) or FALSE/0 (for unsorted data)
=vlookup("bird",A1:J10,10,0)...this will search for "bird" in column A and return the corresponding entry in the 10th column (J)

- the criteria (what you are searching for - bird) MUST be in the 1st column of the data range.
- the data range (A1:J10) MUST include all columns up to and including the column that contains the data to be returned
- the column number(10) MUST be less than or equal to the total columns in your range.

- the format of your criteria must be the same as what is in the 1st column (A)
1 of the biggest mistakes users make is trying to find a text number in a column of real numbers. Text numbers are entries that look like numbers but are actually text...they are often left aligned, compared to real numbers that are generally right aligned.
Just changing the format of a cell does not change it's underlying contents though, formatting is purely a cosmetic affect on a cell

[Mike Huntswarm]

Thank you for replying the detail you did.
I understand that vlookup is a very basic function of excel but yes you are correct with the most common issue please find is the format the data is in.

This is exactly what I am trying to work out as everything I have done to format the data correctly has still posted the N/A value.

For me it has been 2 years since I have used the vlookup function and now with a change of position within my company I have found myself back in a position where i will be using excel much more frequently.

The issue is the data format, I just need a little support on how to convert the data to the correct formats so the formula will populate the fields I need

[AliGW]

Please attach a small sample workbook - instructions at the top of the page.

[FDibbins]

Depends on what your data looks like, you may not need to reformat, perhaps you can restructure the formula.

Can you upload a small sample WB?

[Mike Huntswarm]

After reading through the thread and the advise provided I reviewed the formula string and found that I had been referencing the incorrect starting column from data sheet.

I did not realize that the initial vlookup cell needed to be the first column from the table array.

thank you team I think we can call this one as solved.