Index Match formula changing row numbers to recalculate

[benajamingeldart]

Hi all,

I have a table A:F which searches for the values in column A2 and E2 in the worksheet "Results" and returns the value to cell F2 via the index match code/formula below;
Sub Elementquantity()
'Inserts formula to search for element quantity from conversion sheet based on station ID and element symbol and drags down to last value in column A
Application.ScreenUpdating = False
Dim lastrow As Long
Dim StationIDCol As String
Dim NickelIDRow As Long
Dim ws As Worksheet
Dim i As Integer
Dim j As Integer
Dim rngcol As Variant
Dim rngrow As Integer
Dim ResultsCell As Variant
Dim ResultsCell2 As Variant
Dim Elementsquantity As Variant
Dim ResultsCell3 As Variant

i = 1
j = 1

StationIDCol = Sheets("Cruise Log").Range("H1").value
NickelIDRow = Sheets("Cruise Log").Range("G2").value
rngcol = ((StationIDCol) & ":" & (StationIDCol))
rngcol1 = (StationIDCol)
Set ResultsCell3 = Range("F2")
Set Station = Range("A2")
Set Element = Range("E2")
rngrow = (NickelIDRow)
Set ws = ActiveWorkbook.Sheets("Results")

lastrow = Range("A" & Rows.Count).End(xlUp).Row - 2

ResultsCell3.value = Application.Index(ws.Range("$A$1:$zz$60000"), Application.Match(Range("a2"), ws.Columns(rngcol), 0), Application.Match(Range("e2"), ws.Rows(rngrow), 0)): Sheets("Conversion").Range("F2:F" & lastrow).FillDown


I was hoping this would then drag down the formula into all lower cells and recalulate but instead all this now does is duplicate the same value throughout column F based on the search from A2 & E2. How do i loop the formula through the worksheet increasing the row number until the last number please and thus performing a new search based on the new row and data?
Thanks.

 

[Larry Haydn]

If you are not using vLookup (which searches left to right), then you are trying to
a) find a specific value in column E, return the row number, then
b) find a value in column A in the same row?

 

[benajamingeldart]

It searches the array in the Results! sheet for the row and column when it finds the value in cell A2 & E2 in conversion! sheet. The value in A2 and E2 are long lists and i want to basically put the formula in F2 then drag down and the calculate F3 based on A3 & E2, then F4 based on A4 & E4 until the last row.