Simultaneously Looking up multiple values in a column, and returning another value

[Spyros13]

I have 4 values in my searcher-output lookup array, "(USA)", "(Australia)", "(Canada)" and "United States" (and 4 corresponding outputs, "a","b","c","d") which I want to carry out a wildcard lookup array search of in another column and return the corresponding output associated with the value I was looking for.

So I want it to search for these values in column A,

United States a
(USA) b
(Canada) c
(Australia) d

In this column:

January-Tokelau (New Zealand),Island council and mayoral elections
January-Palestine,Municipal elections
10 January-Georgia (USA),US House and Senate runoff elections
17 January-Tobago (Trinidad and Tobago),Local assembly election (Trinidad Express)
29 January-Haiti,Presidential runoff election
February-Turks and Caicos (UK),Parliament
February-Rodrigues Island (Mauritius),Regional Assembly Election
5 February-Liechtenstein,Parliament (L. Jacquemin 30/1/16)
12 February-Germany,President (by the Parliament)
12 February-Turkmenistan,President (Reuters 15/10/16)


Obviously only the 3rd row, will result in a match on a wildcard vlookup as the other terms are not in there in this example.

This was my first crude attempt by using a formula:

=IF(ISERROR(VLOOKUP("*"&Sheet2!A2&"*",Sheet144!E:E,1,FALSE)),"",VLOOKUP("*"&Sheet2!A2&"*",Sheet2!A:B,2,FALSE))

or this; =VLOOKUP("*"&"(USA)"&"*",E:E,1,FALSE)

but they only identify the first time the value appear and return the actual cell it did appear in.

To get around all the fuss and the need to make an array formula with index/match [a headache and my heads not working for some reason], i did something crude which does the job but crudely (but not as I wanted):

[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD]g[/TD]
[TD]h[/TD]
[TD]i[/TD]
[TD]j[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]









col g=IF(ISNUMBER(FIND("(USA)",E1,1)),1,0)
col h=IF(ISNUMBER(FIND("(Canada)",E1,1)),1,0)
col i==IF(ISNUMBER(FIND("(AUSTRALIA)",E1,1)),1,0)
col j==IF(ISNUMBER(FIND("United States",E1,1)),1,0)

That works, as is good/useful, but I would like something more robust/automatic and applicable to many scenarios.

SO I then adapted some code at work which we use regularly and worked before :

    Sub pullOutCountryCodes()
    '
    ' pullOutPartyCodes Macro
    '
    ' Instructions:
    ' Make sure councillor data is formatted as standard, with Category codes in       column U
    ' Select cells in the Party column (AA) and run the macro. It will look for     party category codes ( now country names)
      ' and insert the relevant party name in the column.
     '
     Dim r As Range
     codes = Array("(USA)", "(Canada)", "(Australia)", "United States")
     party = Array("a","b","c","d")
     For Each r In Selection
     n = 0
     For Each c In codes
                ' Checks for each numeric code in turn in the Category Codes column.
    ' If it finds one, it inserts the corresponding party name in the current cell.
     If r.Offset(0, -6).Value Like "*" & c & "*" Then
     ' commented rows are for debugging and checking results
    '    r.Offset(0, 2).Value = n
    '    r.Offset(0, 3).Value = codes(n)
    '    r.Offset(0, 4).Value = c
    r.Value = party(n)
    End If
    n = n + 1
    Next
    Next
    End Sub

The only bits i changed are in blue above. But it doesnt seem to work. (prior to my adaptation,

 codes=Array("100","124","157","199" ... )

so they were integers

and

 If r.Offset(0, -6).Value Like "*/" & c & "/*" Then

[ so i removed the forward slashes as there wernt any in my column and it shouldnt matter (should it? It doesnt matter in excel. I mean if you ARE looking for [your value] surrounded by "/" then by all means search for it, but if your not, then dont ! So I dont think the missing forward slashes are the problem]

My question is , is that the code SHould work perfectly , but it doesnt (doesnt give me any values). Is it becuase my array codes are text and I have to define them or is it something else!?

Any help on this problem , either as an array formula to put in excel or fixing the code, I would be grateful!

 

[Spyros13]

I should have just phrased it;"How do i get this to work and give me/pull out the values i need?" . It works when the codes are integers.


Sub pullOutCountryCodes()
'
' pullOutPartyCodes Macro

'
Dim r As Range
codes = Array("(USA)", "(Canada)", "(Australia)", "United States")
party = Array("a","b","c","d")
For Each r In Selection
n = 0
For Each c In codes
' Checks for each code in turn in the Category Codes column.
' If it finds one, it inserts the corresponding name in the current cell.
If r.Offset(0, -6).Value Like "*" & c & "*" Then
' commented rows are for debugging and checking results
' r.Offset(0, 2).Value = n
' r.Offset(0, 3).Value = codes
' r.Offset(0, 4).Value = c
r.Value = party
End If
n = n + 1
Next
Next
End Sub[/code]

 

[Spyros13]

Im trying to learn about vba,

interestingly, when i removed the quotes from code=array( ... ) , it produced some funny results (everything was labled 'Australia', the 3rd element in the 2nd column of the array . ! Strange! - why that one!?)

Then I learned more about vba from here: https://www.thespreadsheetguru.com/blog/2014/2/19/what-does-dim-mean-when-coding-vba-macros

and tried this:

 Option Explicit
    Dim codes As String
    Dim party As String
Dim n As Integer
Dim C As Object 'also tried Dim C as Long
Dim r(4, 4) As Double




    Sub pullOutCountryCodes()
    '
    ' pullOutPartyCodes Macro
    '
    ' Instructions:
    ' Make sure councillor data is formatted as standard, with Category codes in column U
    ' Select cells in the Party column (AA) and run the macro. It will look for party category codes ( now country names)
    ' and insert the relevant party name in the column.
    '
    Dim r As Range
    codes = Array("(USA)", "(Canada)", "(Australia)", "United States")
    party = Array("USA", "Canada", "Australia", "USA2")
    For Each r In Selection
    n = 0
    For Each C In codes
    ' Checks for each numeric code in turn in the Category Codes column.
    ' If it finds one, it inserts the corresponding party name in the current cell.
    If r.Offset(0, -6).Value Like "* " & C & "*" Then
    ' commented rows are for debugging and checking results
    '    r.Offset(0, 2).Value = n
    '    r.Offset(0, 3).Value = codes(n)
    '    r.Offset(0, 4).Value = c
        r.Value = party(n)
    End If
    n = n + 1
    Next
    Next
    End Sub

Error : "For Each may only iterate over a collection object or an array"

 

[Spyros13]

This one runs, but is useless

        Option Explicit
    Dim codes As Variant
    Dim party As Variant
Dim n As Integer
Dim C As Variant
Dim r(4, 4) As Double




    Sub pullOutCountryCodes()
    '
    ' pullOutPartyCodes Macro
    '
    ' Instructions:
    ' Make sure councillor data is formatted as standard, with Category codes in column U
    ' Select cells in the Party column (AA) and run the macro. It will look for party category codes ( now country names)
    ' and insert the relevant party name in the column.
    '
    Dim r As Range
    codes = Array("(USA)", "(Canada)", "(Australia)", "United States")
    party = Array("USA", "Canada", "Australia", "USA2")
    For Each r In Selection
    n = 0
    For Each C In codes
    ' Checks for each numeric code in turn in the Category Codes column.
    ' If it finds one, it inserts the corresponding party name in the current cell.
    If r.Offset(0, -6).Value Like "* " & C & "*" Then
    ' commented rows are for debugging and checking results
    '    r.Offset(0, 2).Value = n
    '    r.Offset(0, 3).Value = codes(n)
    '    r.Offset(0, 4).Value = c
        r.Value = party(n)
    End If
    n = n + 1
    Next
    Next
    End Sub

 

[Spyros13]

This is interesting:

excel - VBA- Iterating through string values in an array and identifying if they are equal to a specific text - Stack Overflow

Hyperlink: http://stackoverflow.com/questions/...s-in-an-array-and-identifying-if-they-are-equ

 

[Spyros13]

So is this:

http://www.mrexcel.com/forum/excel-...-applications-loop-through-array-strings.html

but its all way too much. Must be an simpler way to run through a column, look for items/values in another list , and return a corresponding value. I've done it before.

 

[Spyros13]

Solved ! {=VLOOKUP(INDEX(Sheet2!A$1:A$4,MAX(IF(ISERROR(FIND(Sheet2!A$1:A$4,E1)),-1,1)*(ROW(Sheet2!A1:A4)-ROW(Sheet2!A1)+1))),Sheet2!A$1:B$4,2,FALSE)}

:P :P

 

[Spyros13]

Solved ! {=VLOOKUP(INDEX(Sheet2!A$1:A$4,MAX(IF(ISERROR(FIND(Sheet2!A$1:A$4,E1)),-1,1)*(ROW(Sheet2!A1:A4)-ROW(Sheet2!A1)+1))),Sheet2!A$1:B$4,2,FALSE)}

:P :P This forum is awesome/Great!!