0

I have workbook1 with 3 values (columns) concatenated to 1 cell as =TRIM(CONCATENATE("ABC","-",$B174,"-",$A174,"-",$E174)).

Workbook2 has 1 cell that contains the concatenated value from workbook1.

I am performing VLOOKUP inside workbook1 to get data from workbook2 using the concatenated value as the primary key.

However, I get #N/A as a result even if I hard code the actual concatenated value to the VLOOKUP formula.

Am I missing something here?

Improve this question
5
  • If you can't nest it in ( ), then put the concatenated data in a cell and have the VLOOKUP point to the concatenated cell.
    – Wutnaut
    Commented Sep 17, 2014 at 18:45
  • It is still returning #N/A regardless if the VLOOKUP has the concatenated formula or points to the cell with concatenated value.
    – nhotz
    Commented Sep 17, 2014 at 19:08
  • Your question is very unclear. Is there references in the lookup value? If so, you need to pass them as a string. Take a look at my formula here. Is that similar to your problem?
    – Raystafarian
    Commented Sep 17, 2014 at 20:45
  • 1
    To stop losing access to comment on your own question, you should register your account superuser.com/help/creating-accounts
    – random ♦
    Commented Sep 18, 2014 at 14:18
  • Voting to close as unclear. The OP's solution posted below could not have been arrived at from the information included in the question.
    – Excellll
    Commented Jun 16, 2015 at 15:37

3 Answers 3

Reset to default
1

Finally, got it fixed. The problem was the location of the cell in workbook2 which was at the rightmost location. I moved it to the leftmost so it is the first to be read and it worked. Thanks everyone for all your help! Appreciate it! :D

Improve this answer
0

There are probably several options. I would simply copy the concatenated values and past them to the other work book. To perform the paste function, right click on appropriate cell (presumably at the top of a new sheet), then select 'Past Special' and select 'Values' from the resulting menu.

This will past the actual concatenated values to the other workbook, rather then pasting the formula, which would likely return a #ref error.

From there you can do normal vlookup with your pasted special values.

Improve this answer
1
  • i can't paste the concatenated values to the other workbook coz there's a new version generated every other week
    – nhotz
    Commented Sep 17, 2014 at 19:09
0

You can use a concatenated value to perform a lookup like =vlookup("ABC"&"-"&$B364&"-"&$A364&"-"&$E364, 'Some Other Sheet'!A1:B500, 2, false) Assuming that the concatenated value exists in Column A of Sheet "Some other sheet".

If the concatenated value doesn't exist in "Some Other Sheet" you will have to create the concatenated value in a new column (preferably by inserting the column before the data you wish to lookup), and then perform the vlookup off of that newly derived column.

Improve this answer
5
  • I actually have a column that contains the concatenated value and the VLOOKUP is referencing to that column.
    – nhotz
    Commented Sep 17, 2014 at 19:02
  • So, in that case the first formula should do the trick. Just change 'Some Other Sheet'!A1:B500 to the range that contains your concatenated column.
    – JNevill
    Commented Sep 17, 2014 at 19:03
  • It is still returning #N/A regardless if the VLOOKUP has the concatenated formula or points to the cell with concatenated value.
    – nhotz
    Commented Sep 17, 2014 at 19:08
  • Check to make sure that all of your values are trimmed. Often times vlookup will fail because one cell has a value like "SomeValue" and the other cell has "SomeValue ". That extra space will have you tearing your hair out. For starters, in your vlookup, try TRIM("ABC"&"-"&$B364&"-"&$A364&"-"&$E364). If that doesn't work, then in your lookup column/range create a new column that applies =TRIM() to each value to make sure they are clean. (this is just a shot in the dark, but it's the first thing I check with vlookup)
    – JNevill
    Commented Sep 17, 2014 at 19:13
  • i have trimmed both vavlues from both workbooks but stil #N/A :(
    – nhotz
    Commented Sep 17, 2014 at 19:33

Not the answer you're looking for? Browse other questions tagged .