VLookup multiple columns and return to a single cell seperated by commas

  • Hi,


    I am looking for a formula to lookup multiple values and return them in a single cell seperated by commas.
    A sample of the data is as follows - there are 3 columns:
    Code, Batch No, Location
    1,2,A1
    1,2,A2
    2,1,B3
    3,3,C1
    1,2,A1
    1,1,A2


    If i entered the formula in a cell for code 1 and batch no 2 I would like it to return A1,A2. I have been using vlookup and if however it only returns the first value not multiple. Can anybody help me?


    Many Thanks.

  • Re: VLookup multiple columns and return to a single cell seperated by commas


    Use this Custom Function;



    Use in any cell like;


    [frc]=LookupAll(1,$A$1:$C$20,3)[/frc]

  • Re: VLookup multiple columns and return to a single cell seperated by commas


    Hi,


    Thanks i have used that function and it works good but there are 2 issues as it doesnt quite do what i want:
    1) How do i get it to show column 3 only if it matches criteria in both column 1 and 2. I need it to show the locations only if it matches both the code and the batch no.
    2) How do i make it so it ignores duplicate results?


    Thanks heaps.

  • Re: VLookup multiple columns and return to a single cell seperated by commas


    Sorry about the delay, got busy :) Try;


    Used like[frc]=LookupAll(1,"A2",$A$1:$C$20,3)[/frc]

  • Re: VLookup multiple columns and return to a single cell seperated by commas


    I was wondering how I would go about changing the function so that it returns 1 value per cell and can be dragged down so each returned value has its own cell rather than putting all values in one cell separated by commas. In addition, I was wondering how I could name the range of cells that gets returned after the value being matched. (as a heads up, I'm using your original code, for single matches, not the second code you posted.)


    So I have a table like this:



    Dept Code
    C2H GL_PROD_XYZ
    C2H
    C2H GL_DIV_123


    The function works perfectly and returns GL_PROD_XYZ and GL_DIV_123 separated by commas but I'd like to have a range that includes GL_PROD_XYZ and GL_DIV_123 and is named "C2H" so that I can later refer to that range for another lookup down the road.


    I appreciate any help you might be able to give me.

  • Re: VLookup multiple columns and return to a single cell seperated by commas


    Hi


    I'm using your LookupAll function in my code but getting 'Type mismatch' error. I need your help to resolve if I' m using any wrong data. I'm trying to read value from first column of Query4sheet and searching all occurences of it in Column B of another excel (Asc1.xls - range ("B2:C345")) & get the corresponding value from column C in first excel.


    Here is my code -
    ----------------------------


    ----------------------------
    Query4Sheet - Read this file
    TSC1234
    TSC2345


    Range contain - Search in this file
    TSC2346 CHG11306
    TSC1234 CHG11206
    TSC2345 CHG11456
    TSC1234 CHG11276
    TSC1234 CHG11666
    TSC2346 CHG11306
    TSC1237 CHG11306
    TSC1234 CHG11765
    TSC2346 CHG12306
    TSC2346 CHG16606
    TSC2345 CHG13456


    Output Required -
    TSC1234 CHG11206, CHG11276, CHG11666, CHG11765
    TSC2345 CHG11456, CHG12306, CHG13456


    Thanks.


    Can you plz help?

  • Re: VLookup multiple columns and return to a single cell seperated by commas


    Hi,


    I have been trying out your code for lookup all but i am unable to get the result i want.


    Assume that i have 3 columns A, B & C


    A B C
    AU -1 +1
    AU -2 +2
    AU -3 +5
    AU -4 +4
    ETC
    ETC



    I want to return all the positive values to a single cell with the values from column A infront separated by comma when the negative value in B matches with the positive value in C. eg. -1 & +1.


    "lookupall" would return AU1, AU2, AU4 all in a single cell.



    Please help and thanks in advance.

  • Re: VLookup multiple columns and return to a single cell seperated by commas


    Faysalalam,


    PLease do not post your questions in threads started by others. Always start a new thread for your questions and, if it helps to clarify your needs, inlcude a link to this (or any other) thread.


    When creating your thread be sure to give it a title that accurately describes the thread.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!