Forum Discussion
Multiple V-LookUp returns
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...
Hi adobinson95, This formula will get all the matches for each
individual code: =TEXTJOIN(", ",TRUE,IF($A2='[External
Workbook.xlsx]Sheet2'!$A$2:$A$25,'[External
Workbook.xlsx]Sheet2'!$D$2:$D$25,"")) As this is an array formula you
will need to press CONROL SHIFT ENTER (not just enter as you would for a
normal formula). Please note that arrays are heavier than normal
formulas. If, for example, you have 400000 rows of data then the array
would probably crash your Excel file.
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.