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!
Printable View
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!
Welcome to VBAX alancardoso. It would be very helpful to see your current Index Match formula,
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.
I think your formula is still missing. Can you try again please?
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))
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/
Much to my dismay, XL2BB is only available on MrExcel :banghead: :(
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.
LOL, we humans tend to over complicate things at times.