Text function issue

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
9,758
Office Version
2007
Platform
Windows
Sorry, I did'nt translate that formula

Try this:

CellArray 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
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
9,758
Office Version
2007
Platform
Windows
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
Joined
Feb 15, 2019
Messages
11
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
Joined
Dec 3, 2018
Messages
9,758
Office Version
2007
Platform
Windows
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
Joined
Dec 3, 2018
Messages
9,758
Office Version
2007
Platform
Windows
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
Joined
Dec 3, 2018
Messages
9,758
Office Version
2007
Platform
Windows
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),"")
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
9,758
Office Version
2007
Platform
Windows
Sorry, my excel version does not have the Agrggate function, let's hope a moderator can intervene.
 

Forum statistics

Threads
1,086,120
Messages
5,387,961
Members
402,090
Latest member
thomastsiakis

Some videos you may like

This Week's Hot Topics

Top