Using VLookup or Index/Match from two tabs in excel

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