Automatic calculation

[Michaels]

Hi,

I have been having trouble lately using different search functions. I have tried Vlookup, match, Index match etc. and each time I do this it comes up with an error. The values do exist in the source data and they present themselves when I double click on the cell and push enter. Does anyone have a solution to this problem without me having to go in and click enter on every cell (I have about 4,000)

The file I have uploaded is an example - you can see that cell C7 is #NA however the match does exist, if you F2 / double click on A7 and then click enter, the information in C7 is correct. Any suggestions?

Much appreciated.

Michael

 

[Nebu]

Hi:

Find the attached. You had extra spaces in the values you were trying to match, I have used trim function to take out the extra spaces. The formula is more or less same as your formula , but I had to make it array since I had to trim the entire range.(execute the formula by pressing control+shift+enter)

Thanks

 

[Michaels]

Thank you so much - I can't even see that there is an extra space in there (when I click on the cell it doesn't have a space) but that seems to work perfectly, thanks again.

 

[Michaels]

Actually it has worked for most of them but still seem to be a problem for some - I have isolated one as an example, any suggestions?

 

[Nebu]

Hi:

Its working fine in the first tab you forgot to execute it as an array formula.In the 2nd tab you are missing trim function for the range and forgot to execute it as an array formula.
Thanks

 

[Michaels]

excellent, thanks again - what does the array formula do, to make it work?

 

[Nebu]

Hi:

Here is the explanation from Microsoft
https://support.office.com/en-au/ar...formulas-3be0c791-3f89-4644-a062-8e6e9ecee523

Thanks

 

[Michaels]

Thank you. All the best