If statement with vlookup(s) - how to return blank rather than N/A for no results

[easpeed954]

Two questions:

1)

I have an if statement with 2 vlookups and want to augment the formula to return a blank rather than N/A when the lookups yield no results.

Formula I'm using:
=IF(BH61="Parent",VLOOKUP(A:A,A:AI,35,0),VLOOKUP(BH:BH,A:AI,35,0))



2)

I am attempting to concatenate some cells with text strings in the formula but I cannot get a text string that contains quotes (") to work in the formula and thus have to include an absolute cell reference to include the desired text.

So I am using the following formulas:

=CS2&$CT$1&CT2&"</p>"
or
=CONCATENATE(CS2,CT$1&CT2,"</p>"

but would like to instead include the text string rather than the absolute cell reference.

i.e. Excel will not accept the following: =CS2&"<p style="font-size:80%;float:right;padding-right:3%;">Item:"&CT2,&"</p>"


Would like to augment formula to allow <p style="font-size:80%;float:right;padding-right:3%; to replace $CT$1 in the formula


Thank you in advance for anyone who can help.

 

[pjmorris]

Hi easpeed

Firstly welcome to the forum.

Secondly the answer to the first part is =iferror(IF(BH61="Parent",VLOOKUP(A:A,A:AI,35,0),VLOOKUP(BH:BH,A:AI,35,0)),"")

Can you show the source and required output in regard the second query. I'm confused as I believe absolute cell references require $ signs?

Regards

 

[Peter_SSs]

Welcome to the MrExcel board!

You need to double up any internal quote marks.

=CS2&"<p style=""font-size:80%;float:right;padding-right:3%;"">Item:"&CT2&"</p>"

Also, if posting anything that the forum software might interpret as html code, you can enclose that text in HTML tags. The syntax is similar to the Code tags description in my signature block below, or look here

 

[easpeed954]

Thank you so much Peter. I had actually not provided the correct absolute reference in the example I gave you but was using it in my sheet.

Both of your solutions worked perfectly.

I really appreciate it.