Help! VBA Index and Match handling multiple criteria using array formula and variable in index range argument

dylon25

New Member
Joined
Oct 25, 2022
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Forum statistics

Threads
1,223,098
Messages
6,170,103
Members
452,302
Latest member
TaMere

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
Back
Top