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.
If you in sheet 1 called "Table 1" have this in A1:F4
| M4 | P1 | P3 | P4 | P7 | P9 |
| M1 | P2 | P4 | P1 | ||
| M3 | P9 | P3 | P5 | ||
| M2 | P8 | P9 | P4 | P1 |
And in sheet 2 called "Table 2" have this in A1:J5
| P1 | P2 | P3 | P4 | P5 | P6 | P7 | P8 | P9 | |
| M1 | X | X | X | ||||||
| M2 | X | X | X | X | |||||
| M3 | X | X | X | ||||||
| M4 | X | X | X | X | X |
The formula in B2 (in Danish) is:
=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";"")
I think that it should be something like this in an English version:
=IF(IF.ERR(COMPARE(B$1;INDIRECT("'Table 1'!A"&TEXT(COMPARE($A2;'Table 1'!$A$1:$A$4;0);0)):INDIRECT("'Table 1'!Z"&TEXT(COMPARE($A2;'Table 1'!$A$1:$A$4;0);0));0);0)>0;"X";"")
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.
