User Profile
FikturFox
Brass Contributor
Joined 3 years ago
User Widgets
Recent Discussions
Re: Sum rows until new number
beau79au I'm not sure if I got your query right.. =LET(rMain, B15:B30, rQty, E15:E30, rPrice, F15:F30, qPrice, MAP(rQty,rPrice,LAMBDA(q,p, IF(q="",0,q*p))), tPrice, MAP(rMain,LAMBDA(m, IF(m<>"",SUMPRODUCT((OFFSET(m,,3,IFERROR(MATCH(TRUE,OFFSET(m,1,,15)<>"",0),15)))* (OFFSET(m,,4,IFERROR(MATCH(TRUE,OFFSET(m,1,,15)<>"",0),15)))),0))), qPrice+tPrice)Re: Formula Help - Like V Lookup but Multiple Criteria to Match
For column P, depending on the selection (service I guess), it will add the correct pricing per rate from row 9 or 15. So what would be the criteria if you choose row 9 or 15. What's with these rows (not shown in your image)? Could you attached the file please.Re: Formula Help in Excel
balopez66 Your formula should be modified like this: =IFS( A723="Barb", IFNA(VLOOKUP(B723,Kelly,11,FALSE),""), A723="Betty", IFNA(VLOOKUP(B723,Kelly,11,FALSE),""), A723="Bridget", IFNA(VLOOKUP(B723,Kelly,13,FALSE),""), A723="Brad", IFNA(VLOOKUP(B723,Kelly,12,FALSE),""), TRUE,"No Match")Re: Formula Help in Excel
balopez66 Hi. I am not suggesting to wrap your IFS() function with IFNA(). With IFS(), it evaluates every condition that you put. In your example, you have 5 conditions. The 1st one returns False, so it moves to evaluate the 2nd condition, which is again False, then moves to evaluate the next condition (3rd) which is also False. Then comes the 4th condition which evaluates to True, so it then executes VLOOKUP(B723,Kelly,12,FALSE) but returned #N/A. The 5th condition (TRUE, "No Match") is not going to be evaluated because the previous condition (4th) was evaluated to be True. It stops there and moves to execute that vlookup() function which returned #n/a. Should that 4th condition returned False, then you will have an answer of "No Match". Now if you do not want #n/a as a result from the vlookup(), then wrap it with ifna(). ie IFNA(VLOOKUP(B723,Kelly,12,FALSE) , ""), this will now return blank or empty.
