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?

 

[DanteAmor]

Sorry, I did'nt translate that formula

Try this:

[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD]Cell[/TD]
[TD]Array Formula[/TD]
[/TR]
[TR]
[TD]G4[/TD]
[TD]{=IF(IFERROR(VLOOKUP("*"&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]


Remember it is an array formula to accept you must press Shift + Control + Enter

 

[Mchllwoods]

I copy and paste the formula and changed BUSCARV to VLOOKUP. Everything looks right except...
When I update my list and items to my store list my updates don't match up with what I have in my Dashboard. I add a new item from ALDI but it doesn't show in the Dashboard sheet.

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

 

[DanteAmor]

It happens when you have a value = 0, then the minimum is 0.
Use the following Array formula:

{=SI(SI.ERROR(BUSCARV("*"&A4&"*",storesTbl[Item],1,0),0)=0,0,SUMAPRODUCTO(MIN(SI.ERROR(SI.ERROR(HALLAR(A4,storesTbl[Item]),0)/SI.ERROR(HALLAR(A4,storesTbl[Item]),0),99999999)*(SI(storesTbl[Price]=0,9999999,storesTbl[Price])))))}

 

[Mchllwoods]

Thx for your help but the code is kinda long. It did help me make a simpler formula. The formula below works but it doesn't work w/ the store column in the list sheet. When I match up what the INDEX function says compared to the location in the Item column in storesTbl. The name doesn't match up. If u can give me an alternative I would really appreciate it.

{=MIN(IF(ISNUMBER(SEARCH("*"&A4&"*",storesTbl[Item])),storesTbl[Price]))}

 

[DanteAmor]

It happens when you have a value = 0, then the minimum is 0.
Use the following Array formula:

I'm sorry, I forgot to translate


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

 

[DanteAmor]

I'm sorry about the big formulas

=IFERROR(INDEX(storesTbl[Store],MAX(IF(ISNUMBER(SEARCH("*"&A4&"*",storesTbl[Item])), IF( storesTbl[Price] = MIN(IF(ISNUMBER(SEARCH("*"&A4&"*",storesTbl[Item])),storesTbl[Price])), ROW(storesTbl[Item]))))-3),"")

 

[DanteAmor]

Thx for your help but the code is kinda long. It did help me make a simpler formula. The formula below works but it doesn't work w/ the store column in the list sheet. When I match up what the INDEX function says compared to the location in the Item column in storesTbl. The name doesn't match up. If u can give me an alternative I would really appreciate it.

{=MIN(IF(ISNUMBER(SEARCH("*"&A4&"*",storesTbl[Item])),storesTbl[Price]))}

Try this:

=IFERROR(INDEX(storesTbl[Store],MAX(IF(ISNUMBER(SEARCH("*"&A4&"*",storesTbl[Item])), IF( storesTbl[Price] = G4, ROW(storesTbl[Item]))))-3),"")

 

[Mchllwoods]

Thx 4 the info but I figured out a better formula using Aggregate instead of IF and Crtl+Shift+Enter. The only problem is in the list sheet I have a blank store in some rows but adjacent 2 the blanks r Weight and Price values. I got these ideas from Mike Girvin videos. Can someone help me out?

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

 

[DanteAmor]

Sorry, my excel version does not have the Agrggate function, let's hope a moderator can intervene.