How do I handle an IFERROR that returns blank instead of an error?

Matt_has_questions

New Member
Joined
Oct 14, 2020
Messages
1
Office Version
  1. 365
Hi all,

Thanks in advance for your help. My formula is below, I've staggered it to show the nesting a bit more clearly. The final Vlookup is not being triggered because my formula is returning blank

Excel Formula:
=IFERROR(
    IF(V2="","",IFERROR(
    TEXTJOIN(", ",1,VLOOKUP(V2,Props!A:Z,$AH$3,FALSE),VLOOKUP(V2,Props!A:Z,$AH$4,FALSE),VLOOKUP(V2,Props!A:Z,$AH$5,FALSE),VLOOKUP(V2,Props!A:Z,$AH$6,FALSE),VLOOKUP(V2,Props!A:Z,$AH$7,FALSE)),
    VLOOKUP(V2,'Props_all (Opt)'!A:B,$AH$17,FALSE))),"")

Is there an equivalent of IFBLANK, or anyway to make a normal IF statement return the output of the comparison if it's not blank? I suppose having a way to make the TEXTJOIN return an error would also work.

The layout of my sheets are that I have one called Props that contains detailed property addresses, but can miss entries. Then Props_all which doesn't miss any, but only has partial addresses. So I want to lookup the property on Props, and if nothing is returned then fall back to Props_all.

Thanks,
Matt
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
47,960
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
What are the values in AH3 to AH7?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
47,960
Office Version
  1. 365
Platform
  1. Windows
If AH3 to AH7 are consecutive numbers, you could use
Excel Formula:
=IFERROR(TEXTJOIN(", ",1,FILTER(FILTER(Props!A:Z,Props!A:A=V2),(COLUMN(Props!A:Z)>=$AH$3)*(COLUMN(Props!A:Z)<=$AH$7))),VLOOKUP(V2,'Props_all (Opt)'!A:B,$AH$17,FALSE))

Although I would advise against using whole column references, as it can slow down the workbook.
 

Watch MrExcel Video

Forum statistics

Threads
1,112,942
Messages
5,543,124
Members
410,583
Latest member
jgalin
Top