Dec 08 2017
07:14 AM
- last edited on
Jul 25 2018
10:33 AM
by
TechCommunityAP
Dec 08 2017
07:14 AM
- last edited on
Jul 25 2018
10:33 AM
by
TechCommunityAP
Hi,
In column A of workbook 1 I have a number of customer ref codes. I want to do a v-lookup of these codes into workbook 2 to return the orders associated with these codes (located in column D). I want these orders returned to column P of workbook 1.
My problem is there are often multiple orders per ref code. I want these supplementary orders to be either a) returned in the next column (Q), or b) a new row inserted below and the order to be returned to the cell below.
Would anybody be able to help me with this?
Thanks in advance,
Andrew
Dec 08 2017 10:43 AM
Dec 08 2017 10:48 AM
I should point out that the TEXTJOIN function is usually NOT an array formula. However, in your case, you want to get the values only for those that match the current code (row by row).
So...here: IF($A2='[External Workbook.xlsx]Sheet2'!$A$2:$A$25 we have the IF condition and then here: '[External Workbook.xlsx]Sheet2'!$D$2:$D$25 it is concatenating the values from column D.
You will need to adjust the formula according to your workbook names and possibly change the column references.....and as I mentioned before you need to press CONTROL SHIFT ENTER as it's an array formula.
Dec 11 2017 07:39 AM
Hi Kevin,
Thank you for your reply however I'm still having a bit of difficulty with it.
The first [External Workbook.xlsx] - is that the book I'm using or the book I want to retrieve the information from? and vice versa for the second book?
When I try the formula I get the error message #NAME?
Also why is it Sheet 2?
Sorry for all the questions,
Andrew
Jan 17 2018 07:32 PM
Hi Andrew,
What version of Excel are you using? TEXTJOIN function is a new function and maybe it doesn't exist in the version that you are using.
Sorry for such a late response.
Regards,
Kevin