partial text lookup /match in a table and return value with date range and return the month sum value

Batata

New Member
Joined
Jul 7, 2022
Messages
17
Office Version
  1. 365
Platform
  1. Windows
Hello,

I am wondering if someone could help me with the following because I'm stuck with my formula, and I think it's too complicated for my level.

An example below is trying to explain what exactly I want from the table, how I can match a partial text to a table where that table has multiple matching, match the date to that transaction, and have the total value as a return!

How to look up sheet 1 F4 "SUB" partial text in sheet2 D6:D10 for the month in sheet 1 D5:D8 match it with sheet2 C6:C10 and have the return value summation from the amount in sheet2 F6:F10 where I have two value for January month.
And the empty Order No. return as an MAT from sheet 2 to sheet 1 under the MAT header.

Thanks a lot and hope my explanation was clear.

Batata
 

Attachments

  • 1.png
    1.png
    11.1 KB · Views: 16
  • 2.png
    2.png
    20.9 KB · Views: 15

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

ExceLoki

Active Member
Joined
Dec 13, 2021
Messages
288
Office Version
  1. 365
Platform
  1. Windows
to clarify,
  1. you are wanting to sum the values of sheet 2 f6:f10 based on the date in sheet 1 d5:d8 compared to the dates in sheet 2 c6:c10 to be shows in sheet 1 f5:f8?
  2. in sheet 1 e5:e8 you are wanting the order number for that month from sheet 2 d6:d10 or "MAT" to show if there is no date?
as shown below:
1657286249079.png
 

Batata

New Member
Joined
Jul 7, 2022
Messages
17
Office Version
  1. 365
Platform
  1. Windows
ExceLoki,

Thank you for your response; I think it's close to that.

I'm sorry, I guess I did make myself clear enough, let me try again.

if I have the header as SUB and MAT, it will check the shee2 table for partial text " SUB," and ill return the Jan month total amount under SUB in sheet 1

And the same thing for MAT, anything with PO within the month should return the total amount for the month under MAT.

and if there is no input amount for the month, should return "-"

1657290845299.png
=>
1657290860164.png
 

Sufiyan97

Well-known Member
Joined
Apr 12, 2019
Messages
1,107
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
Try

Sheet1

Book3
ABCDEFG
1
2
3
4MonthMatSub
5Jan-2206300
6Feb-2215510
7Mar-22739.21427.2
8Apr-2200
9
10
11
12
13
Sheet1
Cell Formulas
RangeFormula
E5:E8E5=SUMIFS(Sheet2!F:F,Sheet2!D:D,"*"&"PO"&"*",Sheet2!C:C,">="&EOMONTH(D5,-1)+1,Sheet2!C:C,"<="&EOMONTH(D5,0))
F5:F8F5=SUMIFS(Sheet2!F:F,Sheet2!D:D,"*"&"SUB"&"*",Sheet2!C:C,">="&EOMONTH(D5,-1)+1,Sheet2!C:C,"<="&EOMONTH(D5,0))




Sheet2

Book3
ABCDEFG
1
2
3
4
5Posting DateOrder No.NameAmount
61/2/2022BTGWO-SUB-01Engineering6000
71/15/2022BTGWO-SUB-01Engineering300
82/15/2022BTGWO-PO-01Mister Car Wash51
92/1/2022BTGWO-PO-01Ready, Inc.1500
103/1/2022BTGWO-SUB-01Engineering427.2
113/1/2022BTGWO-PO-01People Ready, Inc.739.21
12
13
Sheet2
 

Batata

New Member
Joined
Jul 7, 2022
Messages
17
Office Version
  1. 365
Platform
  1. Windows
Sufiyan97,

Thank you very much for your help, it worked perfectly, Now if I want to add a rental column in sheet1 to have the total amount from sheet2 but for the rental, I have different rentals names so i create a small side table with all the rental company's name and I tried the formula by changing the partial test from "*"&"PO"&"*" to "*"&"F6:F12"&"*" and the look up column from ,Sheet2!D:D, to ,Sheet2!C:C,

but it's not working, Can you help, please?

Thank you

1657298286219.png
=>
1657298724745.png
 

Sufiyan97

Well-known Member
Joined
Apr 12, 2019
Messages
1,107
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
Try

Sheet1

Book1
ABCDEFGH
1
2
3
4MonthMatSubRental
51/1/20225006300500
62/1/2022155100
73/1/2022739.21427.20
84/1/2022000
9
10
11
12
13
14
Sheet1
Cell Formulas
RangeFormula
E5:E8E5=SUMIFS(Sheet2!F:F,Sheet2!D:D,"*"&"PO"&"*",Sheet2!C:C,">="&EOMONTH(D5,-1)+1,Sheet2!C:C,"<="&EOMONTH(D5,0))
F5:F8F5=SUMIFS(Sheet2!F:F,Sheet2!D:D,"*"&"SUB"&"*",Sheet2!C:C,">="&EOMONTH(D5,-1)+1,Sheet2!C:C,"<="&EOMONTH(D5,0))
G5:G8G5=SUM(SUMIFS(Sheet2!F:F,Sheet2!E:E,{"*Sunbelt*","*united rentals*","*enterprise*","*ahern*","*mustang rental*","*stripes & stops*","*buyers barricades*"},Sheet2!C:C,">="&EOMONTH(D5,-1)+1,Sheet2!C:C,"<="&EOMONTH(D5,0)))




Sheet2

Book1
ABCDEFG
1
2
3
4
5Posting DateOrder No.NameAmount
61/2/2022BTGWO-SUB-01Engineering6000
71/15/2022BTGWO-SUB-01Engineering300
81/7/2022BTGWO-PO-30Enterprise Systems Corporation500
92/15/2022BTGWO-PO-01Mister Car Wash51
102/1/2022BTGWO-PO-01Ready, Inc.1500
113/1/2022BTGWO-SUB-01Engineering427.2
123/1/2022BTGWO-PO-01People Ready, Inc.739.21
13
14
Sheet2
 
Solution

Batata

New Member
Joined
Jul 7, 2022
Messages
17
Office Version
  1. 365
Platform
  1. Windows
Sufiyan,

- Thank you again for the amazing help, I was wondering if I can use this instead of typing the Rental inside the form. Cancan I use the table?



- And if I want to have Sheet1 A5 taking the value of sheet 2 B2 according to the month, Can you help me here?
like filling up row 5 in sheet1 from the value in column B sheet 2 for each date.

Thank you

1657307693487.png
==>sheet2
1657307713796.png
 

Sufiyan97

Well-known Member
Joined
Apr 12, 2019
Messages
1,107
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
Sufiyan,

- Thank you again for the amazing help, I was wondering if I can use this instead of typing the Rental inside the form. Cancan I use the table?

Try

Sheet1

Book10
ABCDEFGHI
1
2
3
4MonthMatSubRentalRental with Table
51/1/20225006300500500
62/1/20221551000
73/1/2022739.21427.200
84/1/20220000
9
10
11
12
Sheet1
Cell Formulas
RangeFormula
E5:E8E5=SUMIFS(Sheet2!F:F,Sheet2!D:D,"*"&"PO"&"*",Sheet2!C:C,">="&EOMONTH(D5,-1)+1,Sheet2!C:C,"<="&EOMONTH(D5,0))
F5:F8F5=SUMIFS(Sheet2!F:F,Sheet2!D:D,"*"&"SUB"&"*",Sheet2!C:C,">="&EOMONTH(D5,-1)+1,Sheet2!C:C,"<="&EOMONTH(D5,0))
G5:G8G5=SUM(SUMIFS(Sheet2!F:F,Sheet2!E:E,{"*Sunbelt*","*united rentals*","*enterprise*","*ahern*","*mustang rental*","*stripes & stops*","*buyers barricades*"},Sheet2!C:C,">="&EOMONTH(D5,-1)+1,Sheet2!C:C,"<="&EOMONTH(D5,0)))
H5:H8H5=SUM(SUMIFS(Sheet2!F:F,Sheet2!E:E,"*"&Sheet2!$H$6:$H$12&"*",Sheet2!C:C,">="&EOMONTH(D5,-1)+1,Sheet2!C:C,"<="&EOMONTH(D5,0)))


Sheet2

Book10
ABCDEFGHI
2
3
4
5Posting DateOrder No.NameAmountRental name reference
61/2/2022BTGWO-SUB-01Engineering6000Sunbelt
71/15/2022BTGWO-SUB-01Engineering300United rentals
81/7/2022BTGWO-PO-30Enterprise Systems Corporation500Enterprise
92/15/2022BTGWO-PO-01Mister Car Wash51Ahern
102/1/2022BTGWO-PO-01Ready, Inc.1500mustang rental
113/1/2022BTGWO-SUB-01Engineering427.2stripes & stops
123/1/2022BTGWO-PO-01People Ready, Inc.739.21Buuers barricades
13
14
15
16
Sheet2
 

Sufiyan97

Well-known Member
Joined
Apr 12, 2019
Messages
1,107
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
- And if I want to have Sheet1 A5 taking the value of sheet 2 B2 according to the month, Can you help me here?
like filling up row 5 in sheet1 from the value in column B sheet 2 for each date.

Thank you

View attachment 68921 ==>sheet2 View attachment 68922


@Batata This is a different question, you need to start a new thread for that, you can post link for that new thread here or mention my name like I mentioned yours here with "@"
 

Batata

New Member
Joined
Jul 7, 2022
Messages
17
Office Version
  1. 365
Platform
  1. Windows
@Sufiyan97

Thank you for the table trick; it worked perfectly; thank you for your help; such a strong knowledge and quick response.

Understood, ill open a new thread and mention your name for your help.

Thank you
Batata
 

Forum statistics

Threads
1,186,062
Messages
5,955,622
Members
438,207
Latest member
Excel Pro Tips Guy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top