Vlookup up if the string before # is found and...

[NERYONEIL]

I have this worksheet 2 tabs
Aging
All transactions


Researching customer accounts I want all the data to be easy to understand looking at one single screen.

The research starts if the customer string on "All transaction's TAB" column B after the # has a record that appears on the Column L Aging tab. Then the X column on all transactions Tab will read "YES" or "NO"

My company has customers # with sub accounts (many houses ( -001,003,0014 and so on ) under one single customer account #. I want Excel to look for the string after the "#" and before the "-" in case of having one in Colum B "All transaction's tab" or Colum L Aging Tab.

Manually I did how I want it to look.
I need the formulas on columns X and Y All transactions Tab.


In in AGING TAB Colum L one of the customer string after the "#" is 103833.
If that string "103833" is found on the ALL transactions tab Column B then I want on the all transaction TAb column X to reflect YES, otherwise NO.
On the all transaction TAB I want the open balance column Y to show the result for sustracción of column U and W whenever column G reads “invoice” to show the result as positive but if column G reads other string than "Invoice" then I want to convert to a negative number in red.

Please be sure that the cells formating in column each instance where the # appears will let me filter when I type the customer number on the filter search bar.



Thank you

[AliGW]

The first one is easy. In X2 copied down:

=IF(ISNA(MATCH(LEFT(D2,6)&"*",AGING!$M$2:$M$3,0)),"NO","YES")

Then in Y2 copied down:

=IF(U2="","",-IF(G2="Invoice",0-U2,U2)-W2)

Column Y has been conditionally formatted to show negative numbers in red.

[Bosco]

Or,

[X2] : =IF(COUNTIF(AGING!M$2:M$3,LEFT(D2,6)&"*"),"YES","NO")

[Y2] : =IF(G2="","",IF(G2="Invoice",U2,-W2-U2))

All copied down

Regards

[NERYONEIL]

Good Morning! In orlando FL is 7:13 AM

Any idea why the formula in Y is leaving "Credit Memo" and "Credit card refund" amounts behind, whe I past the formula in a diferent sheet (same columns)?

Thank you!

[NERYONEIL]

Ali I got it is because I didn’t have a 0 column U for some credits memos or credit cards refund.

Bosco for some reason your formula bright me NO to all.

Thank you guys!

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.