Forum Discussion

adobinson95's avatar
adobinson95
Copper Contributor
Dec 08, 2017

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...
Excel Desktop
Formulas & Functions
Kevin Lehrbass's avatar
Kevin Lehrbass
Brass Contributor
Dec 08, 2017
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.
  • adobinson95's avatar
    adobinson95
    Copper Contributor
    Dec 11, 2017

    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

    • Kevin Lehrbass's avatar
      Kevin Lehrbass
      Brass Contributor
      Jan 18, 2018

      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

  • Kevin Lehrbass's avatar
    Kevin Lehrbass
    Brass Contributor
    Dec 08, 2017

    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.

Resources