I've found a solution here: =IF(ISNA(VLOOKUP(A10,A1:B4,2,false)), B4, VLOOKUP(A10,A1:B4,2,false)) Check that B4 is the hardcoded position of the "none" text so this is extracted not using the VLOOKUP it self. I really hope someone comes with something less ugly ;)...

google-spreadsheet,google-docs,gs-vlookup

In B2 try: =ARRAYFORMULA(IF(LEN(A2:A), VLOOKUP(A2:A, 'TITLES'!F$3:G$25, 2,FALSE),)) Make sure all formulas below B2 are cleared. Then try to add a new row and see if this works ?...

In Diet!F2 please try: =100*vlookup(vlookup(E2,Data!A:B,2,0),A:B,2,0)*vlookup(E2,Data!A:D,4,0)/vlookup(E2,Data!A:F,6,0) and copy down to suit....

For your desired output (including the names), try: =ArrayFormula({unique(filter(C2:C, len(C2:C))), vlookup(unique(filter(C2:C, len(C2:C))), sort({C2:C, A2:B, row(A2:A)},4,0), 4, 0)}) Following your thought process (ultimately you want to see if a device is currently checked in or out, right ?), try: =ArrayFormula({unique(filter(C2:C, len(C2:C))), if(vlookup(unique(filter(C2:C, len(C2:C))), sort({C2:C, A2:B, row(A2:A)},4,0), 4, 0) > iferror(vlookup(unique(filter(C2:C, len(C2:C))),...

google-spreadsheet,inventory,gs-vlookup

In short, you can use this formula to get the new inventory numbers =IF(COUNTIF($B$2:B,B2)=1,IF(ISNA(VLOOKUP(B2,Sheet2!$A$2:B,2,0)),C2,VLOOKUP(B2,Sheet2!$A$2:B,2,0)),C2) Refer to new inventory column in this sheet. Detailed Explanation First of all we check if the occurance of the SKU is unique in Sheet 1 or not. This is the first IF condition of our...

google-spreadsheet,average,lookup,gs-vlookup

I think I misunderstood. Try this in cell B2. =iferror(average(filter('Sentiment per URL'!$H$2:$H, ('Sentiment per URL'!$D$2:$D=$A2)*('Sentiment per URL'!$F$2:$F=B$1)))) then drag this formula to the right and down as far as needed. See if that works ?...

if-statement,google-spreadsheet,gs-vlookup

I'm assuming you want to sum column C (quantity of units) when the description matches in column A and col E is > 0 Try using SUMIFS - that should work in both excel and google sheets, i.e. =SUMIFS(C:C,A:A,"Metallic Gold Flash Tattoo Arabic",E:E,">0") ....or do you want to get the...

javascript,google-spreadsheet,spreadsheet,gs-vlookup

@AD:AM I thought that *sign(Row(A2:A)) was required when used inside of an arrayformula for a multi-Column Call. But I just tried it with a brand new sheet and it looks like you're right! And i just tried it out but new sheets still doesn't support Vlookup Multi-Column Calls outside of...