I am not programming in VBA. This is a simple excel spreadsheet. Essentially, I have a formula the finds the lowest price in a range of cells and returns its respective column title. It works fine. However, some ranges do not have any values at all. So, the cell that populates with the column header displays #N/A in this case.
What I need to do is check whether or not this happens. If the result is #N/A, I want the cell to show "No Values to Reference." I was thinking something along the lines of:
=IF(CELL != NULL, Display_Header, ELSE "No Bids to Reference")
OR
=IF(CELL = NULL, No Bids to Reference", ELSE Display_Header)
I have tried both NULL and #N/A but I am not sure how Excel handles these situations. That is, how Excel handles #N/A returns.
IF(INDEX($D$1:$M$1,MATCH(MIN(D3:M3),D3:M3,0))= "#N/A","No Bids To Reference", INDEX($D$1:$M$1,MATCH(MIN(D3:M3),D3:M3,0)))
Since you have only one calculation here, you can simply use an IFERROR
:
=IFERROR(INDEX($D$1:$M$1,MATCH(MIN(D3:M3),D3:M3,0)),"No Bids To Reference")
The IFERROR
checks whether the expression evaluates to an error (it can be #N/A
, #VALUE!
or #REF!
). If true, evaluate the next expression, otherwise return the first expression.
I would CTL+G, Special, and replace the values of all cells with formula errors.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With