Forum Discussion
Help needed with excel formula
In fact it's not very tricky.
COMPARE($A2;'Table 1'!$A$1:$A$4;0) search in column A in Table 1 for the position of the actual M-number in $A2 and returns the row number (lets call it r).
Then I use this row number r to search for the actual P-number (cell B$1) in row r in Table 1.
I search from column A to Z in row r in Table 1, but search from column B would have been ok as well as, as the P-numbers starts in column B and not A.
If the P-number do exist the COMPARE returns the column number - which I don't use - I just want to know that it exist. If the actual P-number don't exist in this row (r) the COMPARE will give an error and that's why I have inserted a IF.ERR around the hole statement.
And then finally I show a X if the P-number exist and an empty text if it doesn't.
Hope that make sense.
In fact it's not very tricky.
COMPARE($A2;'Table 1'!$A$1:$A$4;0) search in column A in Table 1 for the position of the actual M-number in $A2 and returns the row number (lets call it r).
Then I use this row number r to search for the actual P-number (cell B$1) in row r in Table 1.
I search from column A to Z in row r in Table 1, but search from column B would have been ok as well as, as the P-numbers starts in column B and not A.
If the P-number do exist the COMPARE returns the column number - which I don't use - I just want to know that it exist. If the actual P-number don't exist in this row (r) the COMPARE will give an error and that's why I have inserted a IF.ERR around the hole statement.
And then finally I show a X if the P-number exist and an empty text if it doesn't.
Hope that make sense.
I'm a bit irritated by the "COMPARE" which is not an Excel function in English Excel.
Here's a useful site: the Excel Translator will take any Excel function in any localised Excel version and translate it into any other localised Excel version.
https://en.excel-translator.de/translator/
That will take away the need to "invent" translations for function names.
Take the Danish formula
=HVIS(HVIS.FEJL(SAMMENLIGN(B$1;INDIREKTE("'Table 1'!A"&TEKST(SAMMENLIGN($A2;'Table 1'!$A$1:$A$4;0);0)):INDIREKTE("'Table 1'!Z"&TEKST(SAMMENLIGN($A2;'Table 1'!$A$1:$A$4;0);0));0);0)>0;"X";"")
and the correct English translation is
=IF(IFERROR(MATCH(B$1,INDIRECT("'Table 1'!A"&TEXT(MATCH($A2,'Table 1'!$A$1:$A$4,0),0)):INDIRECT("'Table 1'!Z"&TEXT(MATCH($A2,'Table 1'!$A$1:$A$4,0),0)),0),0)>0,"X","")
This is much better than guessing the English names of the functions.
- Thank you for your support.
