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?
53 Answers
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
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.
1You 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.
