This will come in handy while handling data by multiple users. Drop-down ListĪnother cool feature in Excel is adding a drop-down list to categorical data fields.

More information on its usage can be gotten from the Microsoft portal. Similar to Vlookup() there are few other lookup functions in Excel, which are Hlookup(), Lookup(), and Xlookup().

Users can simply use it like a search form rather than scouting through the data. This customized Search/Lookup for data can be implemented in a new sheet and locked for any modification of the formula. where users may want to search for specific data among the humongous rows and tables. This is applicable for any data set such as Sales, Finance, etc. So whichever state I want to search the data for, I simply have to enter in the state field and my vlookup function automatically fetches the result. I have used the vlookup function on two fields namely, Total Public Health Facility and Population. Here is the output that I have created using the vlookup function on the data.

For example for the above-given data set I have the following vlookup function, =VLOOKUP(G47,B1:I38,8,FALSE). The format of the vlookup() function is as follows: =VLOOKUP(What you want to look up, where you want to look for it, the column number in the range containing the value to return, return an Approximate or Exact match - indicated as 1/TRUE, or 0/FALSE). You can insert the vlookup function from the Formulas menu in Excel. For example, in the given data set if you want to quickly find the number of public health centers for a particular state then this function can be modeled to extract only the required data from the entire data set. Also, another use of this functionality is that it can be used as a neat lookup form when many users are working on a particular data set. Vlookup is a handy function to search for specific data when there are many columns and rows in your data set.

Data analysis excel template