Using Wildcard in formula obtained from different formula's results

[jjsauer]

Hello,
I have searched everyone online and cannot find the solution to what I believe is a simple question.


Here is a simple version of what I am trying to accomplish. Sum if the value equals a vlookup. There are four possible outcomes. Option 1, Option 2, Option 3 or All of the above (the vLookup would equal 1,2,3 OR *). I cannot change the formula to be something like greater than 0 because I do not want to combine the three other options unless I am combining them all.

=SUMIF(F33:F35,VLOOKUP($A$3, Sheet4!$C$9:$D$11, 2, FALSE),G33:G35)

However, when the vlookup pulls in the * the formula fails and will not essentially add everything.

I hope that was clear enough. Please keep in mind, this is just an example. The actual formula is a bit more complicated, as well as an array formula.

Any help with piping in an * via vlookup into another formula would be appreciated.

 

[Chrisdontm]

I fail to see what you are referring to as the wildcard, ( "*" ) in your formula.

Could you clarify further, or post a bigger example...?

http://www.mrexcel.com/forum/excel-questions/628649-recommended-add-ins-links.html

 

[Aladin Akyurek]

If the VLOOKUP bit supposed to return a text value including a *, then:

=SUMIF(F33:F35,T(VLOOKUP($A$3, Sheet4!$C$9:$D$11, 2, FALSE)),G33:G35)

When the VLOOKUP returns *, SUMIF will pick out all text values in F33:F35 including a blank (thus, numbers and empty cells in the F-range are excluded).

 

[jjsauer]

I fail to see what you are referring to as the wildcard, ( "*" ) in your formula.

Could you clarify further, or post a bigger example...?

http://www.mrexcel.com/forum/excel-questions/628649-recommended-add-ins-links.html

Hey Chris, what I mean is that I am trying to use a formula that does not include a wildcard hard coded into the formula, but rather on a specific occasion a vLookup will pipe the wildcardto be used into the formula. In some other occasions the wildcard will pipe a 1, 2 or 3. I could create an If statement, but I was trying to avoid this for when I will have to make edits in the future. I don't want to make a mistake since I would have to make this edit twice per formula within the IF syntax.

 

[jjsauer]

If the VLOOKUP bit supposed to return a text value including a *, then:

=SUMIF(F33:F35,T(VLOOKUP($A$3, Sheet4!$C$9:$D$11, 2, FALSE)),G33:G35)

When the VLOOKUP returns *, SUMIF will pick out all text values in F33:F35 including a blank (thus, numbers and empty cells in the F-range are excluded).

Hello Aladin, I am not looking to return a text value, although I code recode my variables to be text. Would that solve my problem? I tried the "?" as well, but that did not work. Basically I need a vlookup that will allow me to run a formula for the following options: IF the criteria equals 1, If the criteria equals 2, if the criteria equals 3, OR if the criteria equals 1 OR 2 OR 3.

Elsewhere I have a dropdown to select a setting, like a filter setting. The formulas currently work with with dropdown for options 1, 2, and 3, but I cannot make the formula run for all three...essentially the "select all" in a filter. Imagine a select the day of the week to filter your data/formula. I can get Monday, Tuesday, Wednesday to work, but I cannot add an 8th option to say Everyday and have the wildcard calc everything that qualifies, which is everything. *I have several other variables that are factoring into this filter and this one particular variable I want to be able to switch on or off. The on would be the criteria 1, 2 or 3. The off, would be everything or all of the above.

 

[Aladin Akyurek]

Does this get you where you want to be?

=SUMIF(F33:F35,SUBSTITUTE(VLOOKUP($A$3, Sheet4!$C$9:$D$11, 2, 0),"*","<>"),G33:G35)

 

[jjsauer]

Does this get you where you want to be?

=SUMIF(F33:F35,SUBSTITUTE(VLOOKUP($A$3, Sheet4!$C$9:$D$11, 2, 0),"*","<>"),G33:G35)

I will look into adding this. Perhaps not exactly with what you have suggested, but this did give me an idea. Maybe I can pipe in the operations command (=, <>, etc.) to fit my needs. That was part of my problem is that I was using the same command, but if I can change that, than I can write this in a way that would work. I will try that at work tomorrow. Thanks!

 

[Aladin Akyurek]

I will look into adding this. Perhaps not exactly with what you have suggested, but this did give me an idea. Maybe I can pipe in the operations command (=, <>, etc.) to fit my needs. That was part of my problem is that I was using the same command, but if I can change that, than I can write this in a way that would work. I will try that at work tomorrow. Thanks!

The SUBSTITUTE bit

SUBSTITUTE(VLOOKUP($A$3, Sheet4!$C$9:$D$11, 2, 0),"*","<>")

replaces the * wildcard the VLOOKUP bit might return with <> which allows SUMIFS to pick out everything in the SUMIF condition range F33:F35 except a cell that is empty/untouched.

 

[jjsauer]

The SUBSTITUTE bit

SUBSTITUTE(VLOOKUP($A$3, Sheet4!$C$9:$D$11, 2, 0),"*","<>")

replaces the * wildcard the VLOOKUP bit might return with <> which allows SUMIFS to pick out everything in the SUMIF condition range F33:F35 except a cell that is empty/untouched.

Hey Aladin, I finally got to work on this formula! This works great when I test your formula in a controlled setting, but fails in my workbook which leads me to my next question. It appears that I may have oversimplified. I need this to work within an Array formula, which means it will need to work within an IF formula. I could not get this to work with a simple IF formula, so I don't think this is the right solution.

Here is one of the formulas I am trying to get this to work:
={SUM(IF(Store!$B$1:$B$1000=AC$2,IF(INDIRECT(VLOOKUP($AA$3, Sheet4!$C:$O, 3, FALSE))<>"BF",IF(INDIRECT(VLOOKUP($AA$3, Sheet4!$C:$O, 6, FALSE))=VLOOKUP($AA$4, Sheet4!$C$9:$D$11, 2, FALSE),INDIRECT(VLOOKUP($AA$3, Sheet4!$C:$D, 2, FALSE))))))}

The part in red is what I am trying to fix to vlookup something to make the formula work as if it was filtering for Option 1 or Option 2 or BOTH in the array.
I know I have a lot of inefficient Vlookups, but they pipe-in named ranges and have been a life saver as this workbook has evolved (rather than editing many pieces of many formulas many times). Therefore, in the formula above, please think of the blue text as normal ranges.

Thanks!!!

 

[Aladin Akyurek]

@jjsauer

Hard to determine what this supposed to do or what data this requires/uses.

Perhaps it's easier to see if you could provide a scaled-down sample.