Hi,
I'm stuck and need some help if you'd be so kind. Here's the lowdown:
sheet1 and sheet2 are in different workbook, sheet1 contains columns of data and I want to use index match with multiple criteria ( 3 columns) using array formula to retrieve from a fourth column and put
that value into Cell C4 in sheet2
the formula I enter into cell C4 in sheet 2 is
=Index(sheet1!F2:F3239,Match(1,($A4=sheet1!b2:b3239)*($B4=sheet!c2:c3239)*(C$3=sheet1!e2:e3239,0))
and it works and correctly retrieves the value from sheet1
When I look at the VB code after entering this formula into cell C4 it reads:
Range("C4").Select
Selection.FormulaArray = _
"=INDEX(sheet1!R2C6:R3239C6,MATCH(1,(RC1=sheet1!R2C2:R3239C2)*(RC2=sheet1!R2C3:R3239C3)*(R3C=sheet1!R3217C5),0))"
However, I need to pass a range variable rng into the first argument instead of a hardcoded range, so that I can have it vary between 5 possible range values. This range variable is in A1 style, so I am trying to convert the above formula into A1 style. I want to replicate the above formula which I know works but in A1 stye and with rng variable containing 5 possible ranges from sheet1 instead of hardcoded sheet1!R2C6:R3239C6,
It should look like this:
Dim rng As Range
rng = sheet1!F2:F3239 'one of the 5 possible range values
Range("C4").Select
Selection.FormulaArray = _
"=INDEX(rng,MATCH(1,($A4=sheet1!B2:B3239)*($B4=sheet1!C2:C3239)*(C$3=sheet1!E2:E3239),0))"
however I get N/A# showing up in cell C4 sheet2 instead of the correct value
I want to literally copy the exact formula in R1C1 style that I know works, because I just had it work in the macro recorder, but in A1 style so it is compatible with the rng variable which is in A1 style
and then use that variable in the first argument of that formula.
Or in other words:
I want the VBA code that will do the same thing as entering the below into Cell C4 in regular excel using macro recorder, but in VB using A1 notation
=Index(rng,Match(1,($A4=sheet1!b2:b3239)*($B4=sheet!c2:c3239)*(C$3=sheet1!e2:e3239,0))
Sorry if that wasn't clear, but i have spent hours stuck on this one formula and am not making progress.
I'm stuck and need some help if you'd be so kind. Here's the lowdown:
sheet1 and sheet2 are in different workbook, sheet1 contains columns of data and I want to use index match with multiple criteria ( 3 columns) using array formula to retrieve from a fourth column and put
that value into Cell C4 in sheet2
the formula I enter into cell C4 in sheet 2 is
=Index(sheet1!F2:F3239,Match(1,($A4=sheet1!b2:b3239)*($B4=sheet!c2:c3239)*(C$3=sheet1!e2:e3239,0))
and it works and correctly retrieves the value from sheet1
When I look at the VB code after entering this formula into cell C4 it reads:
Range("C4").Select
Selection.FormulaArray = _
"=INDEX(sheet1!R2C6:R3239C6,MATCH(1,(RC1=sheet1!R2C2:R3239C2)*(RC2=sheet1!R2C3:R3239C3)*(R3C=sheet1!R3217C5),0))"
However, I need to pass a range variable rng into the first argument instead of a hardcoded range, so that I can have it vary between 5 possible range values. This range variable is in A1 style, so I am trying to convert the above formula into A1 style. I want to replicate the above formula which I know works but in A1 stye and with rng variable containing 5 possible ranges from sheet1 instead of hardcoded sheet1!R2C6:R3239C6,
It should look like this:
Dim rng As Range
rng = sheet1!F2:F3239 'one of the 5 possible range values
Range("C4").Select
Selection.FormulaArray = _
"=INDEX(rng,MATCH(1,($A4=sheet1!B2:B3239)*($B4=sheet1!C2:C3239)*(C$3=sheet1!E2:E3239),0))"
however I get N/A# showing up in cell C4 sheet2 instead of the correct value
I want to literally copy the exact formula in R1C1 style that I know works, because I just had it work in the macro recorder, but in A1 style so it is compatible with the rng variable which is in A1 style
and then use that variable in the first argument of that formula.
Or in other words:
I want the VBA code that will do the same thing as entering the below into Cell C4 in regular excel using macro recorder, but in VB using A1 notation
=Index(rng,Match(1,($A4=sheet1!b2:b3239)*($B4=sheet!c2:c3239)*(C$3=sheet1!e2:e3239,0))
Sorry if that wasn't clear, but i have spent hours stuck on this one formula and am not making progress.
