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

#### Batata

##### New Member
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
11.1 KB · Views: 16
• 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
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:

#### Batata

##### New Member
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 "-"

=>

#### Sufiyan97

##### Well-known Member
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
103/1/2022BTGWO-SUB-01Engineering427.2
12
13
Sheet2

#### Batata

##### New Member
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

=>

#### Sufiyan97

##### Well-known Member
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))

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
113/1/2022BTGWO-SUB-01Engineering427.2
13
14
Sheet2

#### Batata

##### New Member
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

==>sheet2

#### Sufiyan97

##### Well-known Member
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))
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
113/1/2022BTGWO-SUB-01Engineering427.2stripes & stops
13
14
15
16
Sheet2

#### Sufiyan97

##### Well-known Member
- 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
@Sufiyan97

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

Thank you
Batata

Replies
3
Views
88
Replies
0
Views
116
Replies
0
Views
195
Replies
5
Views
186
Replies
2
Views
245

### Forum statistics

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.

### Which adblocker are you using?

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

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