Text function issue

Mchllwoods

New Member
Joined
Feb 15, 2019
Messages
11
I'm making a grocery list in Excel. One sheet has my list then next sheet has a listing of my local grocery stores with items, prices and weight.

I want to search thru the items against what's in my grocery list for the lowest price.

I only have Microsoft Excel 2010. I'm trying to use MIN and SEARCH functions within the SUMIFS function. They r not working. I'm also trying to use AGGREGATE instead of SUMIFS but that's not working either.

What am I doing wrong?
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,880
Is your goal
"This is my list. I am going to buy everything from one store. Which store?"
or
"This is my list. I am going to go to many stores. Which items am I going to buy from which store?"
 

Mchllwoods

New Member
Joined
Feb 15, 2019
Messages
11
I can't send a screenshot. Info too large. If u have OneDrive or GoogleDrive I might be able 2 send a sample that way. This is my first time using Mr. Excel so I'm not too familiar w/ it.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
9,651
Office Version
2007
Platform
Windows
You could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
9,651
Office Version
2007
Platform
Windows
Try this Array formula in sheet List, cell G4

Sheet List

CellArray Formula
G4{=IF(IFERROR(BUSCARV("*"&A4&"*",storesTbl[Item],1,0),0)=0,0,SUMPRODUCT(MIN(IFERROR(IFERROR(SEARCH(A4,storesTbl[Item]),0)/IFERROR(SEARCH(A4,storesTbl[Item]),0),99999999)*(storesTbl[Price]))))}

<tbody>
</tbody>

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>



Copy the formula down.
 

Mchllwoods

New Member
Joined
Feb 15, 2019
Messages
11
where did the BUSCARV function come from cause basic Excel doesn't have it??? Is that a macro? I don't know macros well enough 2 use them.
 

Forum statistics

Threads
1,085,307
Messages
5,382,854
Members
401,807
Latest member
xlWatcher

Some videos you may like

This Week's Hot Topics

Top