The Stack Overflow podcast is back! Listen to an interview with our new CEO.

Hot answers tagged

7

Try formatting Water Tests as Number, Number.


5

Please try: =query(Data!A1:D7,"select * where B ='NO' ")


5

Try using a vlookup: =vlookup(A1,$C$1:$D$7,2,True) This example shows the "values" in column A and the resulting groupings in column B. What's happening here is that the matching parameter in the vlookup has been set to True, and this allows for approximate matching. In the case of numbers, it means it will match to any number that is less than or equal ...


4

Let's say your names are in H1:H4, and your data is in J1:K6. Then to obtain the sum you want use the formula: =sum(arrayformula(vlookup(H1:H4, J1:K6, 2, false))) The key to the solution is obviously ARRAYFORMULA, which takes a single-argument VLOOKUP and iterates it over an array of arguments.


3

Let's look first at why the function you've posted isn't working. VLOOKUP needs a two-dimensional data range to work with. It looks for your search_key (param 1) in the first column of range specified (has to be the first column - an unfortunate limitation of VLOOKUP), and returns a value from the matching row. The column returned is specified by param 3, ...


3

Not pretty, but to convert cell A1 use: =if(A1<6,1,if(A1<10,2,if(A1<16,3,if(A1<22,4,if(A1<27,5,if(A1<32,6,7))))))


3

Try this:B3: =ARRAYFORMULA(IFERROR(VLOOKUP($A$3:$A,SORT(FILTER({'Form Responses 1'!$B$2:$F$7,'Form Responses 1'!$A$2:$A$7},'Form Responses 1'!C2:C7<>""),6,0),B2,0))) Drag fill to the right. It looks big only because of the name of your sheet :( Otherwise, It's a simple blank filter and sorting by A( timestamp) Edit: Formula simplified


2

I've created a Google Spreadsheet in which I tried to interpret your question. With FREQUENCY and plain old values, you can accomplish that. UPDATE: I've chosen the groups more wisely. Result is now exactly the same as the previous answer !! Both scores are 105. Screen shot, with formulas shown: Screen shot, without formulas shown: The file is editable, ...


2

Ok, I found the solution: =INDEX(C:C;MATCH(D6&D7;A:A&B:B;0)) where D6 and D7 are my search criteria...


2

Perhaps something like this: =query(A:C;"select C where A=x and B=y") with x and y replaced by what it is you wish to select. Since sight of your spreadsheet, I am not sure of what you would like where but suggest: insert a column immediately to the right of D in Fleet Log and in E2 enter: =C2&"|"&D2 and copy down to suit. in Sheet5 Cell ...


2

The better way is to use VLOOKUP. =VLOOKUP(D1,A2:B37,2)


2

According to OP (who seems unlikely to post an answer as such): Here is the correct equation: =IF(F1="Test1 1/2",vlookup(A2,Sheet11!A2:F,3,false),IF(F1="Test1 2/1",vlookup(A2,Sheet11!A2:F,4,false),IF(F1="Test2 1/2",vlookup(A2,Sheet11!A2:F,5,false),IF(F1="Test2 2/1",vlookup(A2,Sheet11!A2:F,6,false)))))


2

I think I figured it out myself. This works for 5 columns, but I could add more by extending the formula. =if(isna(C12=vlookup(C12,$G$2:G11,1,false)),C12,if(isna(D12=vlookup(D12,$G$2:G11,1,false)),D12,if(isna(E12=vlookup(E12,$G$2:G11,1,false)),E12,if(isna(F12=vlookup(F12,$G$2:G11,1,false)),F12,""))))


2

I ended up doing this the not-so-smart way: instead of making VLOOKUP happen across multiple columns, I copied all of the English terms multiple times and stacked them vertically in one column, then put the German translations vertically next to them. This wasn't exactly elegant and wouldn't have worked on a much larger data set, but it let me run the ...


2

Use VLOOKUP(). Assuming your lookup table is in H1:I50, you'd put the following in B1: =VLOOKUP(A1,H1:I150,2,FALSE)


1

It looks like you're trying to create a heading in row 1, then below it, run a vlookup if there is a value in column B - This works ={"Country";arrayformula(if(len(B2:B)>0,vlookup(D2:D,FBlookup!$A$3:$B$20,2,false),))}


1

As always as soon as you post help you figure it out yourself. :/ Apparently there is a nifty formula for exactly this. SUMIF allowed me to pick a range (so my unique ID column) , then pick a criteria (my single Unique ID) , and then pick the range of numbers to count the SUM.


1

Short answer Add an auxiliary column to concatenate the values of the two columns, then use the result as reference for the data validation Explanation To concatenate the values of two columns use a formula like the following: =ARRAYFORMULA(Sheet1!B1:B3&IF(LEN(Sheet1!B1:B3)*LEN(Sheet1!C1:C3)>0," ","")&Sheet1!C1:C3)) IF(LEN(Sheet1!B1:B3)*LEN(...


1

Ensure that the material in Main Database is sorted by ascending contents of the first column in it. (It's safest to select the whole range manually and then sort it.)


1

=VLOOKUP (a1,range,2) The problem with that is if the value isn't listed, which most of the time it isn't, it will fail. I want to return the closest value, which VLOOKUP doesn't appear to do First, sort range in ascending order. You could do in place or use SORT Try =VLOOKUP(a1,SORT(range,1,TRUE),2)


1

We are planning to add this feature to Cognito Forms in October/November. Please up vote and comment on this feature on our Idea Board! https://trello.com/c/sgDIKA7o


1

A match is going to be found for lookup or it is not going to be found so two vlookups are not necessary. In general what you seek is not possible, though where you want a response to be the result of one ‘clause’ or a different responses where that ‘clause’ returns an error there is the option of the iferror function. So your formula might be rewritten, and ...


Only top voted, non community-wiki answers of a minimum length are eligible