Another Excel Query

The_Hurricane

Hi All,

This is something that has always bugged me, the following line of code returns a "#NA," is there anyway to prevent this?

=VLOOKUP($A$13,'C drive[Test.xlsx]New Test'!$B:$E,4,FALSE)

Thanks

WTFH

OK, so let's translate it to English...
Take the calue in A13 (fixed to A13, doesn't matter if you fill down, etc)
Then look to the file "Test.xlsx"
Go to the tab called New Test.
Compare the value from A13 with any value in column B of New Test and when it finds an exact match, return the value in column E for the same row.

Why have you gone for $A$13? (this is really odd, and not something I'd do, nor will "normal" Excel code do it - you'd have to type it in yourself.
What is the value in A13?
Is there an exact match for it in column B of the Test.xlsx spreadsheet?

The_Hurricane

WTFH wrote: »

OK, so let's translate it to English...
Take the calue in A13 (fixed to A13, doesn't matter if you fill down, etc)
Then look to the file "Test.xlsx"
Go to the tab called New Test.
Compare the value from A13 with any value in column B of New Test and when it finds an exact match, return the value in column E for the same row.

Why have you gone for $A$13? (this is really odd, and not something I'd do, nor will "normal" Excel code do it - you'd have to type it in yourself.
What is the value in A13?
Is there an exact match for it in column B of the Test.xlsx spreadsheet?

Two different names words were in cell A13 names "Personal Finances," is it not necessary to write it like this? An exact match was not found so an "#N/A" was returned instead, I want this removed.

victor2

You could wrap an IF statement around it. Gets a bit messy, but it would look something like this:
=IF(ISNA(VLOOKUP($A$13,G1:H14,2,FALSE)),"No Match",VLOOKUP($A$13,G1:H14,2,FALSE))

I used a different lookup range in the example, but so long as you repeat whatever you use for the VLOOKUP, it should work. You can then have the "No Match" text displayed where nothing is found, or any other value you like, even nothing at all.
Remember the lookup column must be sorted in ascending order.

WTFH

Your best way of using VLOOKUP is to get Excel to help you.

So, click on the blank cell where you want the answer returned, then if you look at the top left of the spreadsheet, above column A you will see a field which will currently have that cell code in it. Mine says K2 right now. To the right of that field is a downward pointing arrow.
Don't click on it yet - be prepared to be amazed...
In your cell (e.g. K2) put in =
nothing else, just the equals sign
Now, in that field above A, it might say VLOOKUP, or it could say several other functions.
If it says VLOOKUP, then click on the word.
If it doesn't say VLOOKUP, then click on the down arrow and either select VLOOKUP from the list or choose More Functions and search for it in the window that opens up.
Now that you've selected VLOOKUP, you'll have a box in front of you titled "Function Arguments".
The first one is Lookup_value, for that, click in your box A13. DON'T TYPE IN A13, click on the field!
In the Function Arguments box, beside the first field you will now see the value that's in A13.
Now click in the second field - Table Array. Click on the other spreadsheet (Test.xlsx) and highlight the columns you require. So it's B to E
Now click on the Col_index_num and put in the column that contains the value you want to bring back - if it's column E, then that's a 4 (B=1, C=2, D=3, E=4).
Finally, in the Range_lookup , type FALSE

Then click OK

And that's your VLOOKUP done correctly.

WTFH

Now, back to the values...
You are wanting it to find the exact same thing that is in A13 in column B of the other spreadsheet.
If it finds that match, then it will return the value in column E for the same row.
If it does not find that exact match in column B, then it will return #NA

WTFH

victor2 wrote: »

You could wrap an IF statement around it. Gets a bit messy, but it would look something like this:
=IF(ISNA(VLOOKUP($A$13,G1:H14,2,FALSE)),"No Match",VLOOKUP($A$13,G1:H14,2,FALSE))

I used a different lookup range in the example, but so long as you repeat whatever you use for the VLOOKUP, it should work. You can then have the "No Match" text displayed where nothing is found, or any other value you like, even nothing at all.

True, if the issue to get rid of the #NA, then do the above.

victor2 wrote: »

Remember the lookup column must be sorted in ascending order.

That hasn't been required since at least Office 2007 (possibly even Office 2003)

victor2

WTFH wrote: »

That hasn't been required since at least Office 2007 (possibly even Office 2003)

Showing my age I suppose. I know it used to be an issue, and the OP might have an older version!

WTFH

I thought that too, but given he was linking to test.XLSX I'm going to take a stab at a more modern one.

Showing my age too - I was certified in Office 97 - that's 16 years ago!:eek:

victor2

WTFH wrote: »

I thought that too, but given he was linking to test.XLSX I'm going to take a stab at a more modern one.

Showing my age too - I was certified in Office 97 - that's 16 years ago!:eek:

Well spotted!:)

Jivesinger

victor2 wrote: »

You could wrap an IF statement around it. Gets a bit messy, but it would look something like this:
=IF(ISNA(VLOOKUP($A$13,G1:H14,2,FALSE)),"No Match",VLOOKUP($A$13,G1:H14,2,FALSE))

I used a different lookup range in the example, but so long as you repeat whatever you use for the VLOOKUP, it should work. You can then have the "No Match" text displayed where nothing is found, or any other value you like, even nothing at all.
Remember the lookup column must be sorted in ascending order.

WTFH wrote: »

That hasn't been required since at least Office 2007 (possibly even Office 2003)

Yes even Excel 2003 is like this.

However this is a gotcha here... with the parameters as shown, the FALSE at the end means Excel uses an Exact Match and you don't need to sort the data range.

But... if you omit that last parameter or set it to TRUE, then the range does need to be sorted in order of the lookup column, even in the newest shiniest Excel 2013.

From here:
http://office.microsoft.com/en-gb/excel-help/vlookup-function-HA102752820.aspx

range_lookup Optional. A logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match:

• If range_lookup is either TRUE or is omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned.
  
• Important If range_lookup is either TRUE or is omitted, the values in the first column of table_array must be placed in ascending sort order; otherwise, VLOOKUP might not return the correct value.