Forum Discussion
Excel formula not pulling correct data
In my cell# CN I have formula =IFERROR(IF(XLOOKUP($D25,GSS!$A:$A,GSS!$AP:$AP)=0,"-",XLOOKUP($D25,GSS!$A:$A,GSS!$AP:$AP)),"-") and my result should be CFS or FCL. Then in another cell I have formula =IF(CN25="CFS",(CP25),(CO25)), but my result doesn't change based in cell CN. Please help!
2 Replies
Your second formula is valid: it returns CP25 when CN25 equals “CFS”, and CO25 for anything else, including the "-" returned by your first formula.
Start with these checks:
- In an empty cell, enter:=CN25="CFS"If CN25 looks like CFS but this returns FALSE, the lookup result may contain extra spaces or hidden characters.
- To handle ordinary spaces and non-breaking spaces, try:=IF(TRIM(CLEAN(SUBSTITUTE(CN25,CHAR(160)," ")))="CFS",CP25,CO25)
- If the test returns TRUE but the result still doesn’t update, check Formulas > Calculation Options > Automatic, then press F9. Also check that CP25 and CO25 actually contain different values.
If you only want a result for CFS or FCL, with a dash for anything else, use:
=LET( code,TRIM(CLEAN(SUBSTITUTE(CN25,CHAR(160)," "))), IF(code="CFS",CP25,IF(code="FCL",CO25,"-")) )
One other point: XLOOKUP returns the first matching row. If CN25 itself shows the wrong code, check for duplicate values matching D25 in column A of the GSS sheet.
Post few of your sample data. Better to attach or share a dummy file via OneDrive or Google Drive.
