Nested XLOOKUP formula

[janema]

I am trying to get this formula using an XLOOKUP to bring back BLANK if there is an error or no data on the source, but it will only do one or the other. Tried it probably 8 different ways and I can't figure out why it won't work.

=IF(XLOOKUP(R4,'Radford (Life Science 04.2023)'!L:L,'Radford (Life Science 04.2023)'!EG:EG)="","",XLOOKUP(R4,'Radford (Life Science 04.2023)'!L:L,'Radford (Life Science 04.2023)'!EG:EG))
Above is the formula I entered last, but if you see the image, there are still "N/A" results coming back. The blank appears to be working for the most part of instances where the source just has a blank or no data.

 

[SanjayGMusafir]

Have you tried -

=XLOOKUP(R4,'Radford (Life Science 04.2023)'!L:L,'Radford (Life Science 04.2023)'!EG:EG,"")

I suppose it should serve your purpose

 

[janema]

Have you tried -

=XLOOKUP(R4,'Radford (Life Science 04.2023)'!L:L,'Radford (Life Science 04.2023)'!EG:EG,"")

I suppose it should serve your purpose

Unfortunately, the formula you shared doesn't work. I get an error that says "there is problem with this formula."

 

[SanjayGMusafir]

I don't know where it went wrong, I copied your formula and added "" as [if not found value]

You can try that with the original formula in your file

Else we have more ways to do the same

 

[KRice]

The approach described above handles those cases where a match is not found, but I think an issue still exists when a match is found for R4 and the cell to be returned is blank...that will show as a 0 in the return. Also, if a match is found and an error code appears in the column to be returned, the same error code will appear as the answer. To overcome these issues, I would suggest trapping the blanks and errors differently, although it is more convoluted. Here is one approach.

This eliminates the "" (if not found) option in the XLOOKUP. If a match is not found an error will be generated, and that is okay because we will trap it later. If a match is found and an error exists in the cell to be returned, that is also okay...we'll handle the error later. If a match is found and a blank is in the cell to be returned, we'll get a 0 as the answer (more on this). If a match is found and something else valid is found (like any number other than 0), we'll get that answer. So the next step uses an IF function to replace a returned 0 with a blank, otherwise the original answer is retained (including error codes). Then we wrap the entire IF function in an IFERROR function to clean up the error codes and replace them with blanks. The full formula looks like this:

=IFERROR(IF(XLOOKUP(R4,'Radford (Life Science 04.2023)'!L:L,'Radford (Life Science 04.2023)'!EG:EG)=0,"",XLOOKUP(R4,'Radford (Life Science 04.2023)'!L:L,'Radford (Life Science 04.2023)'!EG:EG)),"")

Because there is some redundancy and the worksheet names are rather long, the formula can be reduced in size by using the LET function to define the redundant portion (so that it appears only once and we can use the shortened assigned name (res in this case...short for result) in the formula.

=LET(res,XLOOKUP(R4,'Radford (Life Science 04.2023)'!L:L,'Radford (Life Science 04.2023)'!EG:EG),IFERROR(IF(res=0,"",res),""))

One caveat: if you have actual 0's that should be returned in 'Radford (Life Science 04.2023)'!EG:EG, they will be overwritten with blanks. Is that possible, and if it is, is it acceptable?

 

[Fluff]

Another option is

=LET(x,XLOOKUP(R4,'Radford (Life Science 04.2023)'!L:L,'Radford (Life Science 04.2023)'!EG:EG,""),IF(x="","",x))

 

[KRice]

@Fluff, I may have interpreted the OP's 1st post incorrectly, but when I saw...

to bring back BLANK if there is an error or no data on the source

...I assumed that an error might be due to either a match not being found or perhaps the cell to be returned (in column EG) contains an error already. If the former, then the 4th argument in XLOOKUP specifying what should be returned if a match is not found suffices; but if the latter, then the error will still be returned and something else must be done to replace the error with a blank.

If it is conceivable that errors already exist in column EG, and considering my comment about potentially replacing legitimate 0's with blanks, I like the hybrid solution:

=LET(res,XLOOKUP(R4,'Radford (Life Science 04.2023)'!L:L,'Radford (Life Science 04.2023)'!EG:EG),IFERROR(IF(res="","",res),""))

I think that handles blanks and returns them as blanks, preserves legitimate 0's, and replaces any errors (whether due to not finding a match, or finding a match whose corresponding column EG cell is an error code) with blanks.

 

[Fluff]

I assumed that an error might be due to either a match not being found or perhaps the cell to be returned (in column EG) contains an error already

You could be right, but I would want to know if the return cell contained an error & took it to mean if R4 is not found return "" rather than #N/A

 

[KRice]

Agreed...

 

[janema]

The approach described above handles those cases where a match is not found, but I think an issue still exists when a match is found for R4 and the cell to be returned is blank...that will show as a 0 in the return. Also, if a match is found and an error code appears in the column to be returned, the same error code will appear as the answer. To overcome these issues, I would suggest trapping the blanks and errors differently, although it is more convoluted. Here is one approach.

This eliminates the "" (if not found) option in the XLOOKUP. If a match is not found an error will be generated, and that is okay because we will trap it later. If a match is found and an error exists in the cell to be returned, that is also okay...we'll handle the error later. If a match is found and a blank is in the cell to be returned, we'll get a 0 as the answer (more on this). If a match is found and something else valid is found (like any number other than 0), we'll get that answer. So the next step uses an IF function to replace a returned 0 with a blank, otherwise the original answer is retained (including error codes). Then we wrap the entire IF function in an IFERROR function to clean up the error codes and replace them with blanks. The full formula looks like this:

=IFERROR(IF(XLOOKUP(R4,'Radford (Life Science 04.2023)'!L:L,'Radford (Life Science 04.2023)'!EG:EG)=0,"",XLOOKUP(R4,'Radford (Life Science 04.2023)'!L:L,'Radford (Life Science 04.2023)'!EG:EG)),"")

Because there is some redundancy and the worksheet names are rather long, the formula can be reduced in size by using the LET function to define the redundant portion (so that it appears only once and we can use the shortened assigned name (res in this case...short for result) in the formula.

=LET(res,XLOOKUP(R4,'Radford (Life Science 04.2023)'!L:L,'Radford (Life Science 04.2023)'!EG:EG),IFERROR(IF(res=0,"",res),""))

One caveat: if you have actual 0's that should be returned in 'Radford (Life Science 04.2023)'!EG:EG, they will be overwritten with blanks. Is that possible, and if it is, is it acceptable?

Thank you so much for your help. I don't fully understand the reasoning, but hopefully in time I get it. LOL