Google Sheet Formula

Freelancer Consultant and Trainer for Microsoft Office Applications (Excel, PowerBi, PowerPoint & Word) and Google
Spreadsheet
Mr. JITENDRA H. BHATT
FORMULA Remark Advanced Use

{1,2,3,4} Curly braces denote ARRAYS Append multiple ranges on One another from different Worksheets in any formula
Used to get range from Same file (Same={Sheet1!A:C;Sheet2!A:C}
Sheet / Different Sheet) =QUERY({Sheet1!A:C;Sheet2!A:C}, "SELECT * WHERE Col2 is not null)
{1,2,3,4} Comma for Columns =VLOOKUP(D2,{$B$1:$B$10,$A$1:$A$10},2,FALSE)
{1;2;3;4} Semicolon for Rows
IMPORTRANGE Used to Importrange (data) from differentCombine Importrange with {} you can append data from multiple Files
Files Used in Vlookup formula to Lookup data from different File
After applying formula, you must give =VLOOKUP(A2,IMPORTRANGE("URL","Sheet1!A1:F"),6,FALSE)
Access Right.
QUERY Can change sequence of Columns and Used with VLookup to reorganize columns when Looking answer in Left side.
do many other things like filter, sort, pivot=VLOOKUP(D2,Query(Data,”select B,A,C,D”), 2,FALSE)
with its argument.
If data is from same file use select A, B,
C in syntax
If data is from different file use Col1,
Col2, Col3 in syntax
Freelancer Consultant and Trainer for Microsoft Office Applications (Excel, PowerBi, PowerPoint & Word) and Google Spreadsheet
ARRAYFORMULA It turns original formula into an array, it applies a single formula to every cell in your pre-defined data range. So no need to
allowing you to write only one formula copy your formula down.
and use it across multiple rows =ArrayFormula(IF(A2:A>2000,"Yes","No"))
= ArrayFormula(IF(A2:A)=””,””,IF(A2:A>2000,"Yes","No")))
IF it allows you to make logical comparisons

Depending on TRUE & FALSE give meaning full output
between a value =IF(A2:A>2000,"Yes","No")
VLOOKUP Culprit : 4th Argument (for Exact match 01) Used to Search Database for specific record
place 0 in 4th argument) 02) Used to find the tax Slap using approximate match
03) Used to compare two data set
04) Used to search record from different files Using Importrange Function.
=VLOOKUP(D2,Table range, 4,FALSE)
=VLOOKUP(A2,IMPORTRANGE("URL","Sheet1!A1:F"),6,FALSE)
New Xlookup formula is available now which is more powerful and less error prone
than vlookup
MATCH Remember : provide your position when 01) Can be combine with Sort formula to get custom sort order of data instead
you are in a Queue of ascending or descending Order
It gives position of value in a Queue ( 1D =sort(A2:C10,match(C2:C10,{"Pending";"CDC";"PDC"},0),true)
array) 02) For dependent dropdown list
Culprit : 3th Argument (for Exact match =transpose(index($O$7:$R$12,,match(A7,$O$6:$R$6,0)))
place 0 in 3th argument) 03) Can be used in combination in Vlookup to automatically get the 3rd
argument (Answer Column)
=VLOOKUP(D2,Table range, Match(G2,A2:D2,0),FALSE)
Freelancer Consultant and Trainer for Microsoft Office Applications (Excel, PowerBi, PowerPoint & Word) and Google Spreadsheet
New Xmatch formula is available now which is more powerful and less error prone
than Match
INDEX Remember : In a map when you provide 01) Used to get whole column in dependent dropdown preparation
latitude (row) and Longitude(column) it 02) Used in combination with Match to Overcome limitation of Vlookup formula
provides Location (Cell value)
In a 2D data range if we provide row andIf row is skipped it will provide whole column
column number it will give output Cell If Single column data is selected, Column argument can be skipped
Value. Note: It can not be used with arrayformula
=Index(D2:D, Match(G2,A2:A50,0))
SORT Sort data using formula Used in combination with match formula get custom sort
FILTER Use to get filter data with formula Used with many formula get the filter data
Used to filter columns also by {1,0,1,0}
COUNT Count cells containing Number
COUNTA Count All cells containing number, text,
error
COUNTIF Count Cells depend on specified Criteria
SUMIF Sum depend on specified Criteria

Google Sheet Formula

Uploaded by

Copyright:

Available Formats

Google Sheet Formula

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Google Sheet Formula

Uploaded by

Copyright:

Available Formats

Freelancer Consultant and Trainer for Microsoft Office Applications (Excel, PowerBi, PowerPoint & Word) and Google

Mr. JITENDRA H. BHATT

FORMULA Remark Advanced Use

IF it allows you to make logical comparisons

You might also like