Loop through a column, find a value, return offset value to cell

[harts]

I have been trying to find a solution to my problem. I find bits a pieces that kind of work but not a whole solution. I need to loop the names listed D2:AZ2, find a match in BH1:BH40, then return the value in BF1:BF40 to D3:AZ3. Thank you in advanced. It is much appreciated.

 

[offthelip]

try this code:

Sub test()
Darray = Range("D2:az3")  ' load range D2 to Az3 into a variant array
Bfarray = Range("BF1:Bh40") ' load ranger  BF1 to Bh40 into a varaint array
 For i = 1 To UBound(Darray, 2)  ' loop through each column D to Az
  For j = 1 To UBound(Bfarray, 1) ' 2nd loop through each row 1 to 40
    If Darray(1, i) = Bfarray(j, 3) Then ' compare D2 etc to Bh1 etc
     Darray(2, i) = Bfarray(j, 1)        ' when match found copy to row 3
     Exit For
    End If
  Next j
 Next i
Range("D2:az3") = Darray  ' write the array back to worksheet
 
End Sub

 

[harts]

try this code:

Sub test()
Darray = Range("D2:az3")  ' load range D2 to Az3 into a variant array
Bfarray = Range("BF1:Bh40") ' load ranger  BF1 to Bh40 into a varaint array
 For i = 1 To UBound(Darray, 2)  ' loop through each column D to Az
  For j = 1 To UBound(Bfarray, 1) ' 2nd loop through each row 1 to 40
    If Darray(1, i) = Bfarray(j, 3) Then ' compare D2 etc to Bh1 etc
     Darray(2, i) = Bfarray(j, 1)        ' when match found copy to row 3
     Exit For
    End If
  Next j
 Next i
Range("D2:az3") = Darray  ' write the array back to worksheet
 
End Sub

THAT WORKS PERFECT. THANK YOU FOR YOUR TIME.

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Another way to get the result

Sub test2()
  With Range("D3:AZ3")
    .Formula = "=ifna(index($BF1:$BF40,match(D2,$BH1:$BH40,0)),"""")"
    .Value = .Value
  End With
End Sub

 

[harts]

Both of these work well. Thank you. Thank Peter for the info on updating my information. I will do that. I modified both to do a 2nd look up but have 1 issue. I need to match the name in the 1st row with the name in column BP. Then return the value in column BS. Here is the part I have issues with the name in row1 and the name in column BP are not an exact match. The names in BP are mostly partials. The 1st works on all the cases where it is an exact match, but the 2nd works on the first 2 then stops after that.

Sub test2()
  With Range("D4:AZ4")
    .Formula = "=ifna(index($BS1:$BS40,match(D1,$BP1:$BP40,0)),"""")"
    .Value = .Value
  End With
End Sub

or

Sub test()
Darray = Range("D1:az4")
Bfarray = Range("BP1:BS40")
For i = 1 To UBound(Darray, 1)
  For j = 1 To UBound(Bfarray, 1)
    If Darray(1, i) = Bfarray(j, 1) Then
     Darray(4, i) = Bfarray(j, 4)
     Exit For
    End If
  Next j
 Next i
Range("D1:az4") = Darray
 
End Sub

 

[offthelip]

The reason my code stops after only two matches is because you changed one critical number: if you look at my original code:

For i = 1 To UBound(Darray, 2)  ' loop through each column D to Az

you have changed this to:

For i = 1 To UBound(Darray, 1)

The use of the ubound function is a way of looping through all elements of an array. The second parameter of the ubound function determines which dimension of the array it returns, so my line of code returns the 2nd dimension ( the parameter is set to 2) which in arrays relates to the columns, in your line of code it relates to the 1st dimension ( the paramter set to 1) which relates to rows, so your code only does 4 iterations because it is going from row 1 to row 4. Changing this back to my orginal code shoud solve the probelm of only two matches. However it doesn't solve the problem of the partila match which is a lot trickier

 

[harts]

I see, thank you for explaining that. I changed it and it does work correctly, except for the partial matches

The reason my code stops after only two matches is because you changed one critical number: if you look at my original code:

For i = 1 To UBound(Darray, 2)  ' loop through each column D to Az

you have changed this to:

For i = 1 To UBound(Darray, 1)

The use of the ubound function is a way of looping through all elements of an array. The second parameter of the ubound function determines which dimension of the array it returns, so my line of code returns the 2nd dimension ( the parameter is set to 2) which in arrays relates to the columns, in your line of code it relates to the 1st dimension ( the paramter set to 1) which relates to rows, so your code only does 4 iterations because it is going from row 1 to row 4. Changing this back to my orginal code shoud solve the probelm of only two matches. However it doesn't solve the problem of the partila match which is a lot trickier

 

[harts]

I see, thank you for explaining that. I changed it and it does work correctly, except for the partial matches

If it helps it can be a first word type of match

 

[offthelip]

this will find a match with just the first word, it looks for a space so if there isn't a space it doesn't find a match, but you could combine the two.

Sub test()
Darray = Range("D2:az3")  ' load range D2 to Az3 into a variant array
bfarray = Range("BF1:Bh40") ' load ranger  BF1 to Bh40 into a varaint array
 For i = 1 To UBound(Darray, 2) ' loop through each column D to Az
  Textb = Split(Darray(1, i), " ")
    If IsArray(Textb) Then
    For j = 1 To UBound(bfarray, 1) ' 2nd loop through each row 1 to 40
     Textg = Split(bfarray(j, 3), " ")
   
     If IsArray(Textg) Then
      If Textb(0) = Textg(0) Then ' compare the two first words
       Darray(2, i) = bfarray(j, 1)        ' when match found copy to row 3
       Exit For
      End If
     End If
    Next j
  ' you could add "else" here and then post the first code in here
   End If
 Next i
Range("D2:az3") = Darray  ' write the array back to worksheet
 
End Sub

 

[Peter_SSs]

Thanks for updating your details.

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time in post 5.

Give this one a try

Sub test3()
  With Range("D4:AZ4")
    .Formula = "=LET(bp,$BP1:$BP40,bs,$BS1:$BS40,XLOOKUP(D1,bp,bs,XLOOKUP(TEXTBEFORE(D1,"" "",,,1)&""*"",bp,bs,"""",2)))"
    .Value = .Value
  End With
End Sub