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";"")
- Thank you for the solution. I appreciate your efforts. I didn't know that it would be that tricky. Now, I'll have to devote some time to understand your solution :)
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.
I wish you would have understood my comment and not solved the asker's homework for them.
This encourages people to cheat and ask for solutions instead of working on the solution themselves.
It would have been a much better learning experience for the asker if you had given them the chance to post their own approach and then teach based on that. You have given him a fish that he has not earned and he still does not know how to fish.
- My purpose is to understand how it can be done or solved instead of just getting the solution and not having any clue about what just happened.
- I appreciate your thoughts. I failed to reveal where i got stuck. I was actually trying to do this with index, hlookup and match function. I don't know how to use index function for referencing. The idea that I had in mind was to first match the row (which is simple using the match function which gives relative row value in table 1). And, then to use that reference for searching the spares in that specific row only using index and hlookup function. I'm not very well versed with excel and quite new to this platform, so, posted the problem as it is rather than framing a story around it. I need the idea, guidance, or solution whatever possible for this problem.
