Index Match formula changing row numbers to recalculate

benajamingeldart

New Member
Joined
Dec 10, 2006
Messages
15
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.
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Larry Haydn

Board Regular
Joined
Jul 18, 2019
Messages
180
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Dec 10, 2006
Messages
15
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.
 

Forum statistics

Threads
1,181,081
Messages
5,927,970
Members
436,581
Latest member
Luceroso

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top