SOLVED

IF Advanced

%3CLINGO-SUB%20id%3D%22lingo-sub-1609123%22%20slang%3D%22en-US%22%3EIF%20Advanced%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1609123%22%20slang%3D%22en-US%22%3E%3CP%3ESpreadsheet%20contains%207%20columns%20of%20sales%20data.%20One%20row%20for%20each%20sale%20invoice.%20Column%20F%20contains%20invoice%20information%2C%20starting%20with%20the%20invoice%20number.%20The%20invoice%20numbers%20with%20a%20%22W%22%20indicate%20a%20Warrantee%20item.%20We%20need%20a%20formula%20to%20indicate%20when%20the%20invoice%20number%20contains%20a%20%22W'%20and%20provide%20the%20cost%20from%20Column%20G.%20Something%20like%20%22If%20F3%20contains%20%22W%22%20then%20result%20equals%20G3%2C%20if%20not%20then%200%22.%3C%2FP%3E%3CP%3EI%20have%20attached%20a%20sample%20file.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1609123%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1609187%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Advanced%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1609187%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F769544%22%20target%3D%22_blank%22%3E%40RalphJHart%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20a%20cell%20in%20row%203%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIF(ISNUMBER(FIND(%22W%22%2CF3))%2CG3%2C0)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFill%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1609246%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Advanced%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1609246%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESome%20of%20the%20%22F%22%20cells%20have%20a%20%22W%22%20other%20than%20after%20the%20Invoice%20number.%20Is%20there%20a%20formula%20that%20will%20look%20only%20to%20the%20invoice%20numbers%20ending%20in%20%22W%22%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1609251%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Advanced%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1609251%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F769544%22%20target%3D%22_blank%22%3E%40RalphJHart%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20would%20be%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIF(RIGHT(F3)%3D%22W%22%2CG3%2C0)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1609274%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Advanced%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1609274%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20must%20be%20missing%20something.%20That%20just%20gives%20me%20a%20zero.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1609284%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Advanced%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1609284%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWait%20-%20I%20got%20it.%20The%20challenge%20is%20that%20is%20nearly%20every%20case%20the%20invoice%20number%20is%20followed%20by%20other%20data.%20The%20%22W%22%20is%20always%20the%2016th%20character%20in%20the%20string%20that%20always%20starts%20on%20the%20left%20side.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1609296%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Advanced%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1609296%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F769544%22%20target%3D%22_blank%22%3E%40RalphJHart%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThen%20it%20would%20be%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIF(MID(F3%2C16%2C1)%3D%22W%22%2CG3%2C0)%3C%2FP%3E%3C%2FLINGO-BODY%3E
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

@RalphJHart 

In a cell in row 3:

 

=IF(ISNUMBER(FIND("W",F3)),G3,0)

 

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"?

@RalphJHart 

That would be

 

=IF(RIGHT(F3)="W",G3,0)

@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)
Solution

@RalphJHart 

Then it would be

 

=IF(MID(F3,16,1)="W",G3,0)