Search results

Workbook? And a reply in the VLOOKUP thread, please. If I don’t get one, I’m out. I’m finding a lack of one extremely discourteous.

Still no response. Why???

Try this: =FILTER($B$5:$F$27,(--LEFT($B$5:$B$27,1)<>1) * (LEN($E$5:$E$27)=18) * (IFERROR(SEARCH("DELIVER TO STATE",$E$5:$E$27),0))) Note yours was missing all the requisite parenthesis.

What about the VLOOKUP thread? https://chandoo.org/forum/threads/vlookup-round-up-to-next-number.58513/ Happy to look, but I really would like some feedback in that other thread, which is still hanging ...

What about the VLOOKUP thread???

Because it's not limiting the search - it's just asking for entries that match the search string plus a space and then one further character - all of the rows have at least this. You could try this: =FILTER($B$5:$E$27,(SEARCH("DELIVER TO STATE",$D$5:$D$27)*ISERROR((SEARCH("%",$D$5:$D$27)))))...

You've started another thread and not even acknowledged the help I offered here. Some feedback for the effort I made on your behalf would have been courteous.

Any use at all???

Or this: =IFNA(IFNA(VLOOKUP(F6,$O$6:$O$17,1,0),LOOKUP(F6,$O$6:$O$17,$O$7:$O$18)),$O$6)

Try this: =IFNA(VLOOKUP(F6,$O$6:$O$17,1,0),LOOKUP(F6,$O$6:$O$17,$O$7:$O$18))

Can you please attach a sample workbook? I suspect the whole number format was in fact a text column, but I'd need to see it to be sure.

Where would you be selecting the date? Where should the results appear? Please manually mock up an example. This sort of 'hot clicking' isn't possible in Excel, but you could have some sort of dependent drop-dwon list instead.

Why can't you create a desensitised copy that shows the problem?

Mynda Treacy is an excellent teacher. Google "power bi mynda treacy".

Perfetto - prego!

In AR3 copied across and down: =LET(s,SUMPRODUCT(($A$2:$A$49=$AQ3)*($AN$2:$AN$49=AR$2)),IF(s=0,"",s)) =LET(s;MATR.SOMMA.PRODOTTO(($A$2:$A$49=$AQ3)*($AN$2:$AN$49=AR$2));SE(s=0;"";s))

You also have IFNA, so you an provide a fallback: =IFNA($K$10:$K$16,"")

Because you are testing for text, not an error. Try this: =ISNA($K$10:$K$16) The hashtag followed by N/A will be seen by Excel as an error message, not regular text. Same with #VALUE!, for which you could use =ISERROR($K$10:$K$16)

Here's a SPILL version: =MAP(E9:E16,F9:F16,LAMBDA(e,f,XLOOKUP(f&G$8,'Raw Data'!A2:A14,'Raw Data'!D2:D14,LET(u,UNIQUE(FILTER('Raw Data'!D2:D14,(NOT(ISNUMBER(--'Raw Data'!D2:D14))*(LEFT('Raw Data'!A2:A14,LEN(e))=e)))),IF(ROWS(u)=1,u,"Multiple"))))) The attached contains a breakdown of how the...

The double unury — just forces an array to return numbers and not text. Shout if you want a fuller explanation and I’ll provide it tomorrow.

Try this: =XLOOKUP(F9&G$8,'Raw Data'!$A$2:$A$14,'Raw Data'!$D$2:$D$14,LET(u,UNIQUE(FILTER('Raw Data'!$D$2:$D$14,(NOT(ISNUMBER(--'Raw Data'!$D$2:$D$14))*(LEFT('Raw Data'!$A$2:$A$14,LEN(E9))=E9)))),IF(ROWS(u)=1,u,"Multiple")))

There's no match for this in the Raw Data: 32026DELIVER TO STATE 1 In the Raw Data, you have things like this: 32026001DELIVER TO STATE 1 No match will result in an error.

OK - try this: =IF(OR(LEN(A2)<LEN(A1),A1="Project ID"),"Top Level","")

Try this: =IF(AND(LEN(A2)<>LEN(A1),B2<=B1),"Top Level","")

The problem is that errors can easily happen when copy typing a formula - by copying and pasting it means that no errors are made. And it's actually easier for you just to copy and paste formulae into your post instead of making screenshots, so it's a win-win. Similarly, it's actually easier for...