Forum Discussion
Conditional Formatting Based on a Table
So, I tried the formula with my details and I keep getting an error message.This is what I've put in
=IF(INDEX(Table110,MATCH($AF$3,Table110[Code],0),MATCH($AH4,Table110[#Headers],0))="M",TRUE,FALSE)
I have also tried it with [Code] b4eing my range of cells for the table and it still doesn't work. Have tried a number of changes to the $AF3/$AH$4 parts none of which are working.
Any idea what I've done wrong?
Thanks
The error you are encountering could be due to a couple of possible reasons:
- Make sure that the table range reference (Table110) matches the actual table range in your worksheet. Double-check that the table name and range are correct.
- Verify that the cell references ($AF$3 and $AH4) correspond to the correct cell locations in your worksheet. Ensure that the referenced cells contain the expected values for matching against the table.
Here are a few troubleshooting steps you can follow:
- Check the table range: Select the table in your worksheet, go to the "Table Design" contextual tab in the Excel ribbon, and verify the table name and range. Make sure that the table range matches the one referenced in the formula (Table110).
- Validate the cell references: Double-check the cell references $AF$3 and $AH4 to ensure they are pointing to the correct cells. Confirm that these cells contain the values you expect for matching against the table.
- Evaluate the formula: You can use the "Evaluate Formula" feature in Excel to step through the formula and see how the calculations are performed. This can help identify any potential errors or mismatches. To use this feature, select the formula cell, go to the "Formulas" tab in the Excel ribbon, and click on "Evaluate Formula."
- Test with simplified formulas: As a troubleshooting step, you can try simplifying the formula to focus on one specific part. For example, you can remove the INDEX and MATCH functions temporarily and test the formula with a simple comparison (e.g., =$AF$3="M"). This will help isolate any issues with the table lookup.
By following these steps and double-checking your table range and cell references, you should be able to identify and resolve any errors in the formula. If not works following these steps, please inform about your Excel Version, Operating system, storage medium, etc.
The steps were processed using AI.
- Excellent, thanks. I will try those steps. Just to clarify, the $A$1 and $B2 in the generic formula you gave me are relating to cells in the table ie the cell the player name and the cell stating if M/F? Because I may have gotten that wrong
- The formula was intended to demonstrate the concept of using the INDEX and MATCH functions for table lookups, but it didn't specifically relate to the cells within your table.
To clarify further, the $AF$3 and $AH4 cell references you mentioned are the ones that should correspond to the cells within your table. The $AF$3 reference is typically used for specifying the lookup value, such as the player's name, while the $AH4 reference is often used for specifying the criteria for matching, like "M" or "F" to represent gender.
Please replace $AF$3 and $AH4 with the actual cell references that contain the desired values for the lookup and matching criteria within your table. The text was created with the help of AI.