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!
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,
Remember To Do the Following....
Use [Code].... [/Code] tags when posting code to the thread.
Mark your thread as Solved if satisfied by using the Thread Tools options.
If posting the same issue to another forum please show the link
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?
Remember To Do the Following....
Use [Code].... [/Code] tags when posting code to the thread.
Mark your thread as Solved if satisfied by using the Thread Tools options.
If posting the same issue to another forum please show the link
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
If things don't change they stay the same
Quite often there is a picnic problem (problem in chair not in computer)
"We were not told it was impossible, so we did it."
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.
Remember To Do the Following....
Use [Code].... [/Code] tags when posting code to the thread.
Mark your thread as Solved if satisfied by using the Thread Tools options.
If posting the same issue to another forum please show the link
Posting Permissions
Reply With Quote
