Array or Vlookup?

[Danny54]

Good Afternoon!

Sheet1 a b

Os Type
1 aix svr
2 printer svr
3 windows 8.1 ws


Sheet2 a b c

1 D Os
2 N1 Windows 8.1 Enterprise
3 N2 Lexmark Printer
4 N3 Windows 7 Enterprise 64 bit Edition Service Pack 1
5 N4 AIX 5.2


I have two sheets in one wb. (Sheet1 and Sheet2)

Im trying to set the value in column c on sheet2
by scanning columns b's data by each row using the values in columns a's data(sheet1)

I thought I could use a vlookup but think more it might be a array of some type.
Any suggestions?

Thanks

The net effect would be like this


Sheet2 a b c

1 D Os
2 N1 Windows 8.1 Enterprise ws
3 N2 Lexmark Printer svr
4 N3 Windows 7 Enterprise 64 bit Edition Service Pack 1 n/a
5 N4 AIX 5.2 svr

 

[Danny54]

here's a pic of the spreasheet

 

[DanteAmor]

If you want the formula:

Book1
	A	B	C
1	D	Os
2	N1	Windows 8.1 Enterprise	ws
3	N2	Lexmark Printer	svr
4	N3	Windows 7 Enterprise 64 bit Edition Service Pack 1	n/a
5	N4	AIX 5.2	svr
Sheet2

Cell Formulas
Range	Formula
C2:C5	C2	{=IFERROR(INDEX(Sheet1!$B$2:$B$4,MAX(IF(ISNUMBER(SEARCH(Sheet1!$A$2:$A$4,B2)),ROW(Sheet1!$A$2:$A$4)))-1),"n/a")}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

______________________________
Or the macro:

Sub vlookup_type()
  Dim a, b, c
  Dim i As Long, j As Long
  a = Sheets("Sheet1").Range("A2:B" & Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row).Value2
  b = Sheets("Sheet2").Range("A2:B" & Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row).Value2
  
  ReDim c(1 To UBound(b, 1), 1 To 1)
  For i = 1 To UBound(b, 1)
    c(i, 1) = "n/a"
    For j = 1 To UBound(a, 1)
      If LCase(b(i, 2)) Like "*" & LCase(a(j, 1)) & "*" Then
        c(i, 1) = a(j, 2)
        Exit For
      End If
    Next
  Next
  Sheets("Sheet2").Range("C2").Resize(UBound(c), 1).Value = c
End Sub

 

[Danny54]

Sweet. Thanks for both solutions as they add to my knowledge of doing it using either an Array or Macro.
I was working on the macro solution myself but your code gives me the understanding of either way.
Where I myself was lost was to use the UBound function.
As always, you guys that provide solutions are Super!

Have a Great day and a better weekend.

Thanks

 

[DanteAmor]

I'm glad to help you. I appreciate your kind comments.