Error while using INDEX+MATCH in VBA (based on Excel formula)?

Question

I'm trying to get content of mapping table based on 2 criteria.

Find below the formula I would like to convert

=INDEX(MappingTables!Y2:Y19;MATCH(1;(MappingTables!V2:V100=AE2)*(MappingTables!W2:W100=Y2);0))

Find below the current VBA code I'm using (where RANGE are constants) :

res = Application.Index(mappingTables.Range(RANGE1), Application.Match(1, (mappingTables.Range(RANGE2) = criteria1) * (mappingTables.Range(RANGE3) = criteria2), 0))

However, I'm getting below error

Unidentified error detected. Process terminated.

Can you please help me to understand where is the issue ?

Thank you,

Regards,

The issue is in Excel. No user level code should cause it to crash. — GSerg
– GSerg, Commented Jul 9, 2021 at 9:07
Of course ahah. Maybe it is not possible and should I use Application.Evaluation instead ? — Royce
– Royce, Commented Jul 9, 2021 at 9:09

Pᴇʜ · Accepted Answer · 2021-07-09 09:40:53Z

1

A construct like (mappingTables.Range(RANGE2) = criteria1) * (mappingTables.Range(RANGE3) = criteria2) does not work in VBA, you can only do this with an array of data in formulas.

This mappingTables.Range(RANGE3) is an array of values and VBA cannot compare entire arrays without looping, nor can it multiply entire arrays without looping.

In VBA you would have to loop through that data to calulate the result for each data point in the array.

To avoid that I recommend to use Evaluate and a formula:

res = Evaluate("=INDEX(MappingTables!Y2:Y19,MATCH(1,(MappingTables!V2:V100=AE2)*(MappingTables!W2:W100=Y2),0))")

or better

res = Evaluate("=INDEX(MappingTables!" & RANGE1 & ",MATCH(1,(MappingTables!" & RANGE2 & "=" & Criteria1 & ")*(MappingTables!" & RANGE2 & "=" & Criteria2 & "),0))")

edited Jul 9, 2021 at 9:40

answered Jul 9, 2021 at 9:09

Pᴇʜ

57.9k10 gold badges55 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Royce Over a year ago

Thank you very much. For the moment, this is returning #VALUE! so I guess there is an issue in the arguments provided to the formula. Criteria1 and Criteria2 should not be put between " " ? to avoid to have something like ...=jos dos)*(... where jos dos is Criteria1 ?

Pᴇʜ Over a year ago

@Royce Well then you need to set your criteria like Criteria1 = """jos dos""" so the " are included for text criteria. The criteria could alse be numeric that is why I didn't include the quotes in the formula directly but if you have always text criteria include them in the formula like RANGE2 & "=""" & Criteria1 & """)

Royce Over a year ago

Thank you for your quick feedback. This is strange, the formula is working in a cell (I retrieve it with Debug.Print) but still returning #VALUE! with Evaluate function. Length of the formula cannot impact the result ? My formula contains 97 chars.

Pᴇʜ Over a year ago

@Royce wait, replace the ; in the formula with , the evaluate function only accepts , as delimiter (default US english).

Collectives™ on Stack Overflow

Error while using INDEX+MATCH in VBA (based on Excel formula)?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related