Can Not Get My VLookUp In Excel To Return The Requested Data

Question

Can Not Get My VLookUp In Excel To Return The Requested Data

I am trying to pull data from another sheet based on data selected from a dropdown on the main sheet.

All the formatting is "General"

=VLOOKUP(F15737,'Location Master'!$A:$J,2,FALSE)

It just keeps returning me #N/A

Answer 1

Try using the Index Match method. It's an alternative to Vlookup which doesn't require data to be sorted and can therefore be of more use.

The typical structure of this method is (the text inside the asterisk will give the ranges specific to your sheet:

=INDEX (**Column from which you want to return a value**, (MATCH(**Lookup Value**, **Column against which you want to lookup**,0))

In this case, if I've understood your workbook structure, the formula should look like this:

=INDEX('Location Master'!$B:$B,(MATCH(F15737,'Location Master'!$A:$A,0)))

Answer 2

This is a common problem with VLOOKUP(). Most likely you have some whitespace (A tab character or some spaces) after one of the values. Click on F15737 and see if there are any spaces at the end of it. Likewise, manually find the value in 'Location Master'!$A and check it for spaces or tabs after the value.

If the whitespace is found in F15737 then you can change your vlookup to be:

=VLOOKUP(TRIM(F15737),'Location Master'!$A:$J,2,FALSE)

If the whitespace is in the range to which you are looking up, then you'll need to trim all of those values, which you can do pretty quickly in a new column with the TRIM() formula.

If this doesn't solve the problem then you might have a number stored as text. Generally excel will tell you if this is the case within the cell with a little green corner indicator. To get Excel to automagically change a column from a "Number stored as Text" to a proper number you can:

1. Highlight the column
2. Go to Data>>Text To Columns
3. Click "Fixed Width"
4. Click "Finished"

Excel will then format everything automatically (dates to dates, numbers to numbers, text to text, time to time, etc.)