INDIRECT(MATCH...) Formula pulling into correct place but not correct value

it's not a very good idea to use widely INDIRECT. named ranges, INDEX and MATCH are enought.

I was unable to find an index match formula that would do the job and not slow down the spreadsheet so that it would freeze. I have since added in the helper column so unsure if an index match could be used with the helper column. There are 100000+ rows in the full spreadsheet, not all rows in the data tab will have a match in the items tab. If index match will work please can I have an example that will not freeze up my spreadsheet. Many thanks

all strings must be put down the same way. Best way is using validation lists.

Thank you very much for this, however it seems to be pulling into the wrong column although all the correct values are there they seem to be 1 column over from where they should be.

Originally Posted by AlanY

if you change your formula in E5 to and copy both ways

=IFERROR(INDIRECT("items!"&E$2&MATCH(E$4,INDIRECT($D5),0)+MATCH($A5,items!$A$8:$A$15,0)+6),"")

that should work.

that change your reference cell for INDIRECT related to your start row rather than fixed to +7

Thank you for your help, however I tried this formula before and it froze the spreadsheet. It ran the formula for hours before freezing. I dont think that this is the best option for a spreadsheet with over 100000 rows and 113 columns with this formula in every cell and I am reluctant to try it again as I am sure you will understand. If there is a way of using this simple index match that will not freeze the spreadsheet then I would be keen to try that.

Originally Posted by tim201110

all strings must be put down the same way. Best way is using validation lists.

not quite sure i got it.

could you highlight the problem on the attached

it will be faster to use vba and arrays. But a pair of INDEX and MATCH without {} and with a source table of 6x100 is the fastest way in worksheet functions. And this pair is very clever and isn't volatile, It can't frooze excel app. Look at your hardware

Hi Alan,
So sorry, I must have done something wrong as it seems to be working fine now - so a huge thankyou. I will now try it in my full size spreadsheet and see what happens

Originally Posted by AlanY

not quite sure i got it.

could you highlight the problem on the attached

Originally Posted by carrach

Thank you for your help, however I tried this formula before and it froze the spreadsheet. It ran the formula for hours before freezing. I dont think that this is the best option for a spreadsheet with over 100000 rows and 113 columns with this formula in every cell and I am reluctant to try it again as I am sure you will understand. If there is a way of using this simple index match that will not freeze the spreadsheet then I would be keen to try that.

i notice that on your data(2) sheet, for A10072 you have 2nd class post instead of 1st class on the items sheet

Originally Posted by carrach

Hi Alan,
So sorry, I must have done something wrong as it seems to be working fine now - so a huge thankyou. I will now try it in my full size spreadsheet and see what happens

no problem, good luck on that

Originally Posted by AlanY

no problem, good luck on that

Unfortunately the calculations are too big for my excel to handle so I am having to start again with finding a way to do this. It works in the example spreadsheet but my original is obviously way too big.

Originally Posted by tim201110

it will be faster to use vba and arrays. But a pair of INDEX and MATCH without {} and with a source table of 6x100 is the fastest way in worksheet functions. And this pair is very clever and isn't volatile, It can't frooze excel app. Look at your hardware

Hi tim,
I would like to give the INDEX MATCH another try but not sure what you mean by source table of 6 x 100 - how does this relate to my spreadsheet example?

Originally Posted by tim201110

all strings must be put down the same way. Best way is using validation lists.

Hi Tim,
tried to use your index match formula but it takes over 15 minutes to process in just 1 single column and I have 120+ columns of data that need to process. Therefore this is not the best option but thank you anyway