Help XLOOKUP - PHrobot

[Trident_muh]

I'm using =XLOOKUP formula and here is my formula

=XLOOKUP(C:C,C:C,A:A) . So the value that i want to get is in Column A. now if there is no value in Column A i want the text "N/A" instead. what formula should i use or add to my =XLOOKUP formula?

 

[🤖]

To display "N/A" when there is no value found in Column A, you can use the IFERROR function in conjunction with XLOOKUP. Here's an example of the modified formula:

=IFERROR(XLOOKUP(C:C, C:C, A:A), "N/A")

In this formula, the XLOOKUP function is wrapped within the IFERROR function. If the XLOOKUP function returns an error (indicating no match is found in Column A), the IFERROR function will return "N/A" instead.

 

[Trident_muh]

To display "N/A" when there is no value found in Column A, you can use the IFERROR function in conjunction with XLOOKUP. Here's an example of the modified formula:

=IFERROR(XLOOKUP(C:C, C:C, A:A), "N/A")

In this formula, the XLOOKUP function is wrapped within the IFERROR function. If the XLOOKUP function returns an error (indicating no match is found in Column A), the IFERROR function will return "N/A" instead.

The formula does not work.
What I'm getting on my previous formula is the value "0" if the cell is empty. i want to replace it with "N/A" automatically.

 

[🤖]

The formula does not work.
What I'm getting on my previous formula is the value "0" if the cell is empty. i want to replace it with "N/A" automatically.

Apologies for the confusion. If you are getting a value of "0" instead of "N/A" when the cell is empty, it means that the cell is not actually empty but contains a zero. In that case, you can modify the formula to check for both zero and empty cells using the IF and OR functions. Here's the updated formula:

=IF(OR(ISBLANK(XLOOKUP(C:C, C:C, A:A)), XLOOKUP(C:C, C:C, A:A)=0), "N/A", XLOOKUP(C:C, C:C, A:A))

This formula uses the ISBLANK function to check if the XLOOKUP returns a blank cell, and the OR function to combine that check with another check for a zero value. If either of these conditions is true, the formula returns "N/A". Otherwise, it returns the result of the XLOOKUP.

 

[Trident_muh]

Apologies for the confusion. If you are getting a value of "0" instead of "N/A" when the cell is empty, it means that the cell is not actually empty but contains a zero. In that case, you can modify the formula to check for both zero and empty cells using the IF and OR functions. Here's the updated formula:

=IF(OR(ISBLANK(XLOOKUP(C:C, C:C, A:A)), XLOOKUP(C:C, C:C, A:A)=0), "N/A", XLOOKUP(C:C, C:C, A:A))

This formula uses the ISBLANK function to check if the XLOOKUP returns a blank cell, and the OR function to combine that check with another check for a zero value. If either of these conditions is true, the formula returns "N/A". Otherwise, it returns the result of the XLOOKUP.

Wow, Thanks a lot Mr.Robot it helps a lot.

 

[🤖]

Wow, Thanks a lot Mr.Robot it helps a lot.

You're welcome! I'm glad I could help. If you have any more questions, feel free to ask.