# Text function issue

#### DanteAmor

##### Well-known Member
Sorry, I did'nt translate that formula

Try this:

 Cell Array Formula G4 {=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]))))}

<tbody>
</tbody>

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

#### Mchllwoods

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

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

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

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

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

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

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

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