X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Formula/array Help - Wildcard Character, Office 2010    
post Jun 13 2018, 08:17 AM

Posts: 218
Joined: 11-May 04

I have a formula that will return an array of locations base on one set value. I am trying to fix the formula to allow for variations to the set value rather than creating a formula for each possibility. The set value can be different on the second character.

The formula that finds the set value = FXFR13350300000 is =IFERROR(INDEX(Analysis!$B$1:$B$899, SMALL(IF(Analysis!$A$1:$A$899=$A$3, ROW(Analysis!$B$1:$B$899),""), ROW()-2)),"")
The above formula will return all the locations that has that value.

I would like the formula to look at the second space as I wild card and I would like to capture those locations as well = F*FR13350300000 I have tried this formula but it doesn’t seem to work. The cell where I enter it into blanks out with no result: =IFERROR(INDEX(Analysis!$B$1:$B$899, SMALL(IF(REPLACE(Analysis!$A$1:$A$899,2,1,”*”)=$A$3, ROW(Analysis!$B$1:$B$899),""), ROW()-2)),"")

I have been told that this change to the formula should work but I can’t seem to make it return the result needed. I only get a blank cell. Any help would be greatly appreciated.

Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    25th May 2019 - 10:09 PM