I am trying to create a conditional formatting rule for a table which looks up a pass mark in another table and then compares the value to that pass mark. It is meant to be as general as possible because columns will be added later.
My desired outcome is this:
Sheet 1 (Table2):
Sheet 2 (Table4):
The ticks show pass or fail.
Currently, to make this example for sheet1. I have the following in each cell:
=XLOOKUP(INDEX(Table2[#Headers],COLUMN()-MIN(COLUMN(Table2))+1),Table4[[Name]:[Name]],Table4[[Pass Mark]:[Pass Mark]],"Exam name not found"). This clearly works because the correct numbers appear in each cell.
When I copy paste this into the formatting rule dialog, I get "Please enter a valid formula". Why? The applied area is the entire worksheet.
Update: If I remove the Table syntax and use this formula =XLOOKUP(INDEX($1:$1,COLUMN()-1+1),'Pass Marks'!$A:$A,'Pass Marks'!$B:$B,"Exam name not found"), then it's no longer invalid. However, the ticks and crosses change seemingly at random when I add new data or change existing cell values!
excel file: https://file.io/HYIm2m8r875I
=ISNUMBER(D3)or convert to numbers on the fly with=D3+0.=XLOOKUP(C$1,'Pass Marks'!$A:$A,'Pass Marks'!$B:$B,0)+0assuming you start your CF in cell C2. Make sure it is C$1 and not $C$1 or Excel won't update the relative reference correctly. Also the "not found" text return might cause errors for the numerical comparison so rather use 0 or whichever for your default condition. The +0 at the end helps to convert any "text" formatted numbers to actual numbers.