Vlookup to ignore blanks and only return when there is a value

Question

I have a pivot table that there is a sales lead in column B that I need associated with the invoice total line (col F). I have added helper text columns in D & E, and want to pull the lead over to the line with the TOTALS. When I use the following Vlookup; it works some of the time but not all as it will fill in with a blank value OR with the sales lead:

=IF(D3<>" ",VLOOKUP(E3,A:E,4,FALSE))

Any suggestions?

Answer 1

Hi @Kristene Couture

Could you please show us the results that you want for better analysis?

Currently I suggest you do not move "Total" text when you make Column E ("Text Inv #").

Then please try formula =IFERROR(IF(FIND("Total",E2),INDEX(D:D,ROW(E2)-1,1),""),"") in F2, the Sales Lead name will filled in to Invoice Total lines.

If you also want to show Sales Lead name when Column D (Text Sales Lead) is not blank, please try formula =IF(D2="",IFERROR(IF(FIND("Total",E2),INDEX(D:D,ROW(E2)-1,1),""),""),D2) in F2.

Any misunderstanding, please let me know.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

---