Using VLookup or Index/Match from two tabs in excel-CodePudding

I wanted to perform the following using a VLOOKUP:

If value in column E (MMA Uploader sheet tab) is found in column B (MMA Product List sheet tab)...
Paste value of column A (MMA Product List sheet tab) into column D (MMA Uploader sheet tab)

Here is a visual example:

MMA Uploader tab:

MMA Product List tab:

Places appropriate ID (in MMA Uploader tab) since VLOOKUP has found a matching name:

This is what I have so far with my VLOOKUP function (testing it in cell D2 of the MMA Uploader tab):

=IFERROR(VLOOKUP("*"&E2&"*",'MMA Product List'!$A:$B,1,FALSE), "N/A")

This is what I have so far with my INDEX MATCH function (testing it in cell D2 of the MMA Uploader tab):

=IFERROR(INDEX('MMA Product List'!$A:$A,MATCH("*"&E3&"*", 'MMA Product List'!$B:$B,0)), "N/A")

Neither of the functions I wrote above result in a value, they both return #N/A.

As always, thank you for your help!

CodePudding user response：

If you're using VBA, you can use Range.Find and work with the sheet objects directly, instead of going through formulas to retrieve values.

Here is an example of a simple loop through column E, using Range.Find to look in the other sheet's column B and copy over values:

Dim wsUp As Worksheet
Set wsUp = Sheets("MMA Uploader")

Dim wsPL As Worksheet
Set wsPL = Sheets("MMA Product List")

'Finding the last non-blank cell in Uploader Column E
Dim LastRow As Long
LastRow = wsUp.Cells(wsUp.Rows.Count, 5).End(xlUp).Row

'Loop though Uploader Column E from row 2 till the last non-blank cell
Dim i As Long
For i = 2 To LastRow
  Dim rFind As Range
  'Looking in Product List column B for each value of Uploader column E
  Set rFind = wsPL.Columns(2).Find(what:=wsUp.Cells(i, 5), LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True)
  
  'If a value is found, then copy from column A into Uploader column D
  If Not rFind Is Nothing Then
    wsUp.Cells(i, 4).Value = rFind.Offset(0, -1).Value
  End If
Next i