Convert Index Match to a regular excel reference

[alancardoso]

Hi guys. I have a large spreeadsheet with multiple index matches. However, the sheet now is very slow. Is there anyway I can convert the index match to regular excel references without doing it manually? Thanks for the help!

[Aussiebear]

Welcome to VBAX alancardoso. It would be very helpful to see your current Index Match formula,

[alancardoso]

Thanks for the warm welcome!

The formula below matches the selected row and column cells with the data in sheet "Input Data". Instead of using this formula I want to convert it to the cell its specifically referring to (which is cell S138 in this particular case). I want to avoid doing it manually as I will need to convert hundreds of formulas.

[Aussiebear]

I think your formula is still missing. Can you try again please?

[alancardoso]

Ops. Sorry. The formula is:
=INDEX('Input Data'!$B$1:$AF$253, MATCH(B69, 'Input Data'!$B$1:$B$253, 0), MATCH(P1, 'Input Data'!$B$1:$AF$1, 0))

[alancardoso]

I got XL2BB to work to make it easier to understand the formula.

The excel formula is here: https://www.mrexcel.com/board/threads/convert-index-match-to-a-regular-excel-reference.1238707/

[georgiboy]

Much to my dismay, XL2BB is only available on MrExcel

[Jan Karel Pieterse]

You can make your model a lot faster if you put both match formulas in a separate row and column respectively.
This is more efficient as you no longer recalculate the same MATCH for every row and for every column

Like in the attached example.

[Aussiebear]

LOL, we humans tend to over complicate things at times.