VLOOKUP NOT Returning Proper Data

[Karen615]

I'm using Excel 2007

I posted this a few weeks ago and I'm still having problems with it

Look at the VLOOKUP formula in the "Data.xlsx" file
Why is this not working?

I need to return the marketfrom column D in the Markets file based on the Co. & Acct. Combo in column C


HELP!

[judgeh59]

my guess is the your CONCENATE is creating a STRING not a NUMBER.....So, when you Lookup and compare the Co & acct combo they don't match...

you can add a * 1 to the end of the CONCATENATE and that forces XL to make it a number

[FDibbins]

Concat always produces a text answer

[FDibbins]

Try this, you wont need the helper columns...
=INDEX([Markets.xlsx]FG!D$2:D$18, MATCH(A3&C3, INDEX([Markets.xlsx]FG!A$2:A$18& [Markets.xlsx]FG!$B$2:$B$18,0),0))

Note that your data in A and C does not change until row 253, so the answers are all the same

[Karen615]

Thank you FDibbins & JudgeH59!

Your formula worked great when I opened Markets.xlsx and Data.xlsx, but when I use both files that I have on our company network, it returns an error
The name that you entered is not valid. Reasons for this include:
The name does not begin with a name or underscore
The name contains a space or other invalid characters
The name conflicts with an Excel built-in name or the name of another object in the workbook

I copied the entire path into the formula. Do you know what I'm doing wrong?

=INDEX([F:\Accounting\InventoryPurchases\Markets.xlsx]FG!D$2:D$18,MATCH(A3&C3, INDEX([F:\Accounting\InventoryPurchases\Markets.xlsx]FG!A$2:A$18& [F:\Accounting\InventoryPurchases\Markets.xlsx]FG!$B$2:$B$18,0),0))

I also added a *1 to the end of the CONCATENATE formula.

HELP!

[judgeh59]

I could guess....I believe you can use your original formulas and just add the *1 to the end....not both solutions....my solution fixes the looking up a string to number and Ford's fixes in the formula itself...

so I would try going back to your original VLOOKUPS and adding the *1 to the CONCATENATE formula and see where that puts you...HTH....

[FDibbins]

Another suggestion would be to have both files open, then copy the formula where it needs to go (or better yet, reconstruct it) in the correct file. Then save both files, close them and then reopen and see if it still gives a problem. Done that way, excel will (should) put in the correct path to the file

[Karen615]

Thank you FDibbins & JudgeH59
I will give both solutions a try and get back to you.
Thank you for your time!
Karen