Text function issue

[Mchllwoods]

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?

 

[kvsrinivasamurthy]

Pl upload a sample screen shot showing data and the expected results with manual calculations.

 

[mikerickson]

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]

Which items I'm going to buy from which store.

 

[Mchllwoods]

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]

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.

 

[Mchllwoods]

https://www.dropbox.com/s/ip00gmb0rpff9qh/Groceries.xlsx?dl=0

Here is the document. As u can c, I'm adding up anything that has cereal but what I really want is to find the lowest priced item. That way I can limit my grocery trips or order online.

 

[DanteAmor]

Try this Array formula in sheet List, cell G4

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Sheet List

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Cell[/TD]
[TD]Array Formula[/TD]
[/TR]
[TR]
[TD]G4[/TD]
[TD]{=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]))))}[/TD]
[/TR]
</tbody>[/TABLE]

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself
[/TD]
[/TR]
</tbody>[/TABLE]



Copy the formula down.

 

[Mchllwoods]

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.

 

[sandy666]

BUSCARV() is a VLOOKUP