Excel Formula Cheet Sheet Series - Lookup and Logical Formulas

Introduction

Welcome to our Excel blog Series Notes! We delve into the intricate realm of Excel, focusing on two robust tools that can significantly amplify your data analysis and decision-making capabilities: the Lookup function and Logical functions. Whether you are an experienced Excel user or just embarking on your Excel journey, comprehending these functions can streamline your processes and unveil valuable insights buried within your data. This blog shows a glimpse of information in Excel functions and can be used as a reference in our career journey. 

Lookup Function

The Lookup function in Excel serves as a versatile mechanism tailored to seek specific values within a range and retrieve corresponding data from either the same range or another. Among the most renowned Lookup functions is VLOOKUP, denoting "Vertical Lookup." With VLOOKUP, you can swiftly locate and extract information from vast datasets, proving invaluable for tasks like report generation, inventory management, or sales tracking. Additionally, the HLOOKUP function conducts a similar operation by scanning rows horizontally. The following are some of the Lookup functions found in Excel.

 =CHOOSE(UserValue, Item1, Item2, Item3 through to Item29)

This function picks from a list of options based upon an Index value given to by the user.

 =HLOOKUP(ItemToFind,RangeToLookIn,RowToPickFrom,SortedOrUnsorted)

This function scans across the column headings at the top of a table to find a specified item.

=INDEX(RangeToLookIn,Coordinate)

This function picks a value from a range of data by looking down a specified number of rows and then across a specified number of columns. It can be used with a single block of data, or non-continuous blocks.

=INDIRECT(Text)

This function converts a plain piece of text that looks like a cell address into usable cell difference. This address can be either on the same worksheet or on a different worksheet.

=LOOKUP(WhatToLookfor, RangeToLookIn, RangeToPickFrom)

This function looks for a piece of information in a list, and then picks an item from a second range of cells.

=MATCH(WhatToLookFor,WhereToLook,TypeOfMatch)

This function looks for an item in a list and shows its position. It can be used with text and numbers. It can look for an exact match or an approximate match.

kloook

=TRANSPOSE(Range)

This function copies data from a range, places in it in a new range, turning it so that the data originally in columns is now in rows, and the data originally in rows is in columns. The transpose range must be the same size as the original range. 

=VLOOKUP(ItemToFind,RangeToLookIn,ColumnToPickFrom,SortedOrUnsorted)

This function scans down the row headings at the side of a table to find a specified item. When the item is found, it then scans across to pick a cell entry.

Logical Functions

Conversely, Logical functions in Excel, such as IF, AND, OR, and NOT, empower you to execute operations based on specific conditions. These functions equip you with the ability to introduce decision-making logic into your spreadsheets. For instance, the IF function can determine diverse calculations or messages to display contingent on whether predefined criteria are met. The following are some of the Logical functions found in Excel.

 =AND(Test1,Test2)

This function tests two or more conditions to see if they are all true.

 =IF(Condition,ActionIfTrue,ActionIfFalse)

This function tests a condition.

If the condition is met it is considered to be TRUE.

If the condition is not met it is considered as FALSE.

Depending upon the result, one of two actions will be carried out.

=NOT(TestToPerform)

This function performs a test to see if the test fails. (A type of reverse logic).

If the test fails, the result is TRUE.

If the test is met, then the result is FALSE.

 =OR(Test1,Test2)

This function tests two or more conditions to see if any of them are true.

It can be used to test that at least one of a series of numbers meets certain conditions.

Normally the OR() function would be used in conjunction with a function such as =IF().

Conclusion

Proficiency in these functions not only saves time spent on manual searches but also ensures the precision and timeliness of your data. By amalgamating both Logical and Lookup functions, you can formulate intricate formulas adept at handling multiple conditions and scenarios, thereby enriching the dynamism and discernment of your data analysis.