0

Tables

Is it possible to get all the values from Table 1 corresponding to the values in the cell of Table 2 without using VBA ?

For example. In table 2 one value is "India Australia" (F3). I need to get the result as "IND AUS" looking from the table 1.

Thanks in advance!

Anto Thomas
  • 15
  • 5

3 Answers3

3

Using TEXTJOIN:

 =TEXTJOIN(" ",TRUE,VLOOKUP(FILTERXML("<a><b>"&SUBSTITUTE(F3," ","</b><b>")&"</b></a>","//b"),A:B,2,FALSE))

If one does not have TEXTJOIN then VBA is the only way to get it in one cell. See Here for a UDF that mimics TEXTJOIN: VLOOKUP with multiple criteria returning values in one cell

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • Thanks Scott for the reply. I tried using it but unfortunately its is not working. I think its the array formula which is not working. I used Ctrl+Shift+Enter and it curly brackets appears too. But when I checked the formula using Evaluate Formula the array doesn't appear. It just gives the 1st element. – Anto Thomas May 25 '20 at 08:01
1

1] For Office 365 and Excel 2019 user, to use CONCAT function

In G3 array formula copied down :

=CONCAT(IF(ISNUMBER(SEARCH($A$3:$A$6,F3)),$B$3:$B$6,"")&" ")

This is an array formula and needs to be confirmed by pressing with Ctl + Shift + Enter.

enter image description here

2] For all Excel version user, try this longer formula

Create a range name

  1. Select G3 >> Define name >>
  2. Name : Abb
  3. Refers to : =IF(ISNUMBER(SEARCH($A$3:$A$6,$F3)),$B$3:$B$6,"")
  4. OK >> Finish

Then

In G3, enter formula and copied down :

=TRIM(IFERROR(INDEX(Abb,1),"")&" "&IFERROR(INDEX(Abb,2),"")&" "&IFERROR(INDEX(Abb,3),"")&" "&IFERROR(INDEX(Abb,4),"")&" "&IFERROR(INDEX(Abb,5),""))

enter image description here

bosco_yip
  • 3,762
  • 2
  • 5
  • 10
0

I borrowed this VBA solution from extendoffice.com. It's very simple, easy to set up, and worked really well for my situation.

My scenario -

  • Sheet 1 contains the lookup value in column A where I want the formula results in column B.
  • Sheet 2 contains the table array in columns A and B where column A is again the lookup value and column B contains the values to be returned in the results.

First, press ALT+F11 to bring up the VBA editor.

Second, paste this Function into Module1 within the VBA editor:

Function MYVLOOKUP(pValue As String, pWorkRng As Range, pIndex As Long)
    'Updateby Extendoffice
    Dim rng As Range
    Dim xResult As String
    xResult = ""
    For Each rng In pWorkRng
        If rng = pValue Then
            xResult = xResult & ", " & rng.Offset(0, pIndex - 1) 'I changed EO's delimiter from a space to a comma+space.
        End If
    Next
    If Left(xResult, 2) = ", " Then
        xResult = Right(xResult, Len(xResult) - 2) 'This removes any leading commas that make it into the results.
    Else
        'All is well in the world!
    End If

    MYVLOOKUP = xResult
End Function

Third, double click on the cell you want the multi-results in and paste this formula. 

=MYVLOOKUP(A2, 'SheetName'!A1:B1816, 2)

Fourth, edit the formula to fit your spreadsheet.

  • A2 - The lookup value that will be compared against the other list.
  • 'SheetName'!A1:B1816 - Replace SheetName with the name of the sheet your other list is on and replace A1:B1816 with the full range of values that are both being looked up and returned.
  • 2 - If the values you want returned in your results are in a column other than B, replace '2' with the column that contains the values you want returned in your results.

You're done! If you did everything correctly (and you don't have any Trust Center settings blocking VBA code) then it should look like a VLOOKUP returned multiple results in a single cell.

Joshua Barker
  • 113
  • 1
  • 8