IF Advanced

New Contributor

Spreadsheet contains 7 columns of sales data. One row for each sale invoice. Column F contains invoice information, starting with the invoice number. The invoice numbers with a "W" indicate a Warrantee item. We need a formula to indicate when the invoice number contains a "W' and provide the cost from Column G. Something like "If F3 contains "W" then result equals G3, if not then 0".

I have attached a sample file. 


Thank you

6 Replies


In a cell in row 3:




Fill down.

@Hans Vogelaar 


Some of the "F" cells have a "W" other than after the Invoice number. Is there a formula that will look only to the invoice numbers ending in "W"?


That would be



@Hans Vogelaar 


I must be missing something. That just gives me a zero.

@Hans Vogelaar 



Wait - I got it. The challenge is that is nearly every case the invoice number is followed by other data. The "W" is always the 16th character in the string that always starts on the left side. 

Best Response confirmed by RalphJHart (New Contributor)


Then it would be