Combining Formulas in a Cell

[Houstonwolf]

Hello. I have a worksheet that compares vendor shipments to depot receipts. I have been using the VLOOKUP command mostly successfully (actually this: =IF(ISNA(VLOOKUP(J29,'Receipt Audit - 0828-0911.xls'!$D:$E,2,0)),0,(VLOOKUP(J29,'Receipt Audit - 0828-0911.xls'!$D:$E,2,0))) in order to return a zero where an entry is not found).

One of my vendors sometimes adds suffixes to their purchases orders and -- thanks to the members on this board! -- I found that =SUMIF('Receipt Audit - 0828-0911.xls'!$D:$D,J41&"*",'Receipt Audit - 0828-0911.xls'!$E:$E) will encompass all the PO's whether or not a suffix is included.

However, in some cases the =SUMIF will return double the quantity actually received (that's due to internal coding of the original report from which the Receipt Audit report is exported), so it doesn't work in all cases or I'd use it instead of the VLOOKUP.

My question is can a formula be formatted so that it would apply the SUMIF and if the result was double the vendor's shipment it would apply the VLOOKUP and if the result was N/A it would return a 0?

Thank you again everybody. I really appreciate everyone on this board.

 

[fairwinds]

Your column B holds a combination of text and numbers. I believe the VLOOKUP picks up only the texr as the lookup value is obtained by concatenation thus text.

Copy column B to e.g. column Z. Then in column B put the formula:

=Z434&""

 

[Houstonwolf]

I had hoped that would work, but Excel returns an error when I try the formula =G2&'''' or =(G2&''''). Does it work on your end?

 

[Domenic]

I had hoped that would work, but Excel returns an error when I try the formula =G2&'''' or =(G2&''''). Does it work on your end?

It looks like you're using apostrophes ' instead of quotes ". For quotes you'll need to hold down the SHIFT key and then press the key containing the apostrophe and quote...

=G2&""

Hope this helps!

 

[Houstonwolf]

D'oh!

Thank you!