Same Formula returning a blank cell

H.L.Dixon2021

Hi, I am working on updating an error message to return "No Resource Assigned" however the same formula is now returning a blank cell. Both columns have the same properties (contact list), and this same IFERROR(VLOOKUP) formula has worked on multiple rows prior to this blank cell and continues for the rest of the columns I am trying to fix.

=IFERROR(VLOOKUP(Brand@row, {Client Assignments for Affordability Progr

Range 1}, 10, false), "No Resource Assigned")

=IFERROR(VLOOKUP(Brand@row, {Client Assignments for Affordability Progr

Range 3}, 7, false), "No Resource Assigned")

Any help is greatly appreciated!

Paul Newcome

Do you have an entry for "No Brand" in your lookup table?

H.L.Dixon2021

No Brand is not referenced in any of my other worksheets. This was added manually to the cells that were previously blank. The weird thing is that I have multiple columns with the same IFERROR(VLookup) to return No Resource Assigned if # No Match result is returned, however its not working on these columns.

H.L.Dixon2021

In this screen shot you can see that it returned the result for No Brand in the Affordability BD column and also for Creon.

Paul Newcome

Can you provide a screenshot of the formula in the sheet itself?

H.L.Dixon2021

Returning blank results

Returning Results

Paul Newcome

You have two different ranges which leads me to believe you are matching in two different columns on Brand@row?

Melissa Boehl

Try Index, Match to replace the VLookup. So much better and I have had less issues like this when using the index match with the iferror. This doesn't address the specific issue you are having but I think it will help in this sheet and future formulas.

H.L.Dixon2021

Also I can copy and paste that same formula that is returning the correct result from the Affordability BD column to the Affordability Back up PGM column and its still blank

H.L.Dixon2021

I have since removed the formula, saved the document and rewrote the formula and now I am returning blank cells no matter what I use.

Paul Newcome

Are you able to provide a screenshot of the reference table?

H.L.Dixon2021

Paul Newcome

Ok. So generating a blank means you are getting a match on the brand but that the column you are pulling from is blank in the reference sheet. Double check your ranges to ensure you are definitely pulling from the correct columns. I also suggest rewriting to INDEX/MATCH for a number of reasons to include not having to worry about if the source columns get rearranged.

H.L.Dixon2021

The formula is not returning any results now for any rows :O

Paul Newcome

It actually is returning a result. Just not the expected result. Double check your reference sheet and cross sheet references and definitely look into replacing VLOOKUP with INDEX/MATCH.