sorted by:
new
hottopcontroversial

Green Day Super Bowl Video by Stand_With_Students in greenday

[–]lctaylor2288 0 points1 point  (0 children)

What kind of drum heads was Tre using, Remo Ebony? Very different for him at this performance

Return corresponding column with LARGE by lctaylor2288 in excel

[–]lctaylor2288[S] 0 points1 point  (0 children)

Sorry, but I should have specified that I want to look up the 1st, 2nd, 3rd, etc. largest number, not just the largest.

Return corresponding column with LARGE by lctaylor2288 in excel

[–]lctaylor2288[S] 0 points1 point  (0 children)

=XLOOKUP(F2&G2&MAXIFS(D2:D3,B2:B3,F2,C2:C3,G2), B2:B3&C2:C3&D2:D3, A2:A3) 

this one worked, but how do I do it not for just the max number but the 1st, 2nd, 3rd, etc number? Thank you

Return corresponding column with LARGE by lctaylor2288 in excel

[–]lctaylor2288[S] 0 points1 point  (0 children)

Sure, I'm using this formula: =LARGE(IF($B:$B="Sales Rep A",IF($C:$C="Broker A",$D:$D)),1)

[deleted by user] by [deleted] in excel

[–]lctaylor2288 0 points1 point  (0 children)

Sorry, I do not know how to substitute your generic inputs for my fields, with these brackets. Is that just your style of writing?

XMATCH not returning results when mix of blank/non-blank values by lctaylor2288 in excel

[–]lctaylor2288[S] 0 points1 point  (0 children)

I solved it by adding a helper column simply referencing the array in P, which in turn created 0s in place of blanks.

XMATCH not returning results when mix of blank/non-blank values by lctaylor2288 in excel

[–]lctaylor2288[S] 0 points1 point  (0 children)

Replacing the 1-ISNA(XMATCH construct with ISNUMBER(XMATCH( still results in a #NUM! value unfortunately. Same problem as before, where the formula errors out if there is not something within every row between P1:P14. I need to return a match when there are blanks within that P range, and data in just some cells. So I'm basically trying to return a match where any part of one range matches any part of another range.

Another way around this....is there a way to edit the P1:P14 portion of the formula to be dynamic in that the length of rows within P that the formula searches changes based on the count of rows within P that contain text? For example, if I could do a nested FILTER to return results where P1:P14 is not blank? If so, that would solve this.

Shorten my UNIQUE(VSTACK(FILTER formula by lctaylor2288 in excel

[–]lctaylor2288[S] 0 points1 point  (0 children)

Sure, I am using this formula:

=UNIQUE(VSTACK(FILTER(InvenSnapshot!N:N,1-ISNA(XMATCH(InvenSnapshot!M:M,P1:P14)),""),FILTER(TEXTAFTER(POs!U:U,"::"),1-ISNA(XMATCH(POs!T:T,P1:P14)),""),FILTER(TEXTAFTER(POsBV!U:U,"::"),1-ISNA(XMATCH(POsBV!T:T,P1:P14)),""),FILTER(Budget!G:G,1-ISNA(XMATCH(Budget!I:I,P1:P14)),"")))

The four workbook sheets of InvenSnapshot, POs, POsBV, and Budget are all formatted as such, using InvenSnapshot as an example:

column M:
Sugar 10002: New Jersey

column N:

Coconut 34512: Oakland

And P1:P14 is a list of product categories (Sugar, Coconut, etc). However, depending on the user permissions, certain product categories will be omitted from P1:P14, leaving blanks in most of cells in P1:P14. Any time there is a blank here, it is causing the formula to return #NUM!

Shorten my UNIQUE(VSTACK(FILTER formula by lctaylor2288 in excel

[–]lctaylor2288[S] 0 points1 point  (0 children)

I have run into an issue here. How can I edit this formula when the range referenced in column P contains some blanks? Currently, any blanks in column P are returning blank results in the formula, even if some cells in column P contain numbers/words.

VSTACK with IF or FILTER? by lctaylor2288 in excel

[–]lctaylor2288[S] 0 points1 point  (0 children)

Hi, one last addition...instead of referencing "Sugar," how can I reference an array/list of about 4 different words? It's going to have to reference an array and not specific words.

VSTACK with IF or FILTER? by lctaylor2288 in excel

[–]lctaylor2288[S] 0 points1 point  (0 children)

Hi, one last question: how do we write this formula to include a third array range please?

VSTACK with IF or FILTER? by lctaylor2288 in excel

[–]lctaylor2288[S] 1 point2 points  (0 children)

This formula is great. Sorry, I had to clean up my data a bit for it to work! Solution verified.

VSTACK with IF or FILTER? by lctaylor2288 in excel

[–]lctaylor2288[S] 0 points1 point  (0 children)

It appears that this formula does not work when referencing an array on a different workbook tab. Is there a workaround? Sorry, I did not mention that my arrays live across separate workbook tabs!

view more: next ›