Jyggalag
Active Member
- Joined
- Mar 8, 2021
- Messages
- 445
- Office Version
- 365
- 2019
- Platform
- Windows
Dear all,
I am currently having some issues with a mix of IFERROR, INDEX, MATCH and TRANSPOSE and ROWS formula in Excel:
I have the current sheet of data:
The formula and the data links to this test sheet of data:
Now I have done about 90% of the work (I hope), seen as my IferrorIndexMatchRows formula correctly links to the test data and grants me the number I want for No. of Companies (stated as No. of comparables in test sheet, since I want to ignore if they have different names here) and I get the number 20 in Cell C3 in my test data sheet.
However, if I
pull the data down alongside my column C in my test sheet, as you can all see, it keeps returning the value 20. I think I can fix this in the final INDEX-formula by not binding cell J1 in my test data sheet, which means that if I pull the data horizontally (as seen in my small test example marked in blue in my Test sheet) I get the correct numbers.
The issue that I am facing is that I want to be able to pull my data DOWN and still get the correct results. I think I need to use a transpose formula for this and I have so far tried to do it myself without much success.
Can anybody help me on what my formula should look like please? I have taken an old formula I used 1 year ago, so I also do not understand my formula very well, I just know that it works. Apologies for the inconvenience! Any assistance would be GREATLY appreciated!!
Please let me know if you need more data or information!
I am currently having some issues with a mix of IFERROR, INDEX, MATCH and TRANSPOSE and ROWS formula in Excel:
I have the current sheet of data:
The formula and the data links to this test sheet of data:
Now I have done about 90% of the work (I hope), seen as my IferrorIndexMatchRows formula correctly links to the test data and grants me the number I want for No. of Companies (stated as No. of comparables in test sheet, since I want to ignore if they have different names here) and I get the number 20 in Cell C3 in my test data sheet.
However, if I
pull the data down alongside my column C in my test sheet, as you can all see, it keeps returning the value 20. I think I can fix this in the final INDEX-formula by not binding cell J1 in my test data sheet, which means that if I pull the data horizontally (as seen in my small test example marked in blue in my Test sheet) I get the correct numbers.
The issue that I am facing is that I want to be able to pull my data DOWN and still get the correct results. I think I need to use a transpose formula for this and I have so far tried to do it myself without much success.
Can anybody help me on what my formula should look like please? I have taken an old formula I used 1 year ago, so I also do not understand my formula very well, I just know that it works. Apologies for the inconvenience! Any assistance would be GREATLY appreciated!!
Please let me know if you need more data or information!
