In this article, we are telling about useful commands in Excel that can make you a better Excel master.
This command is used to look for the data in the same rows of many columns. For instance, you have 100 items of assortment on sales in your store and want to quickly find out the price of some without the need to find every position by manual search. Also, this is useful if you don’t know whether there are any items at all on the data massive that you’re looking for.
This command looks like
- D1 is the cell with your search criteria;
- A1:C100 is the range to look for;
- 2 is the number of the column of the search range (can start with 1 and ends with the max number of columns in the sheet);
- 0 defines whether to give out the exact or approximate result (if approximate, then put 1 but the output will not be precise and can be basically any).
Thus, if you want to find the result for ‘Apple’ in the table below using this formula, it will be ‘5.40’
The real fun begins when you have large arrays of data to search for and to search from – hundreds, thousands, or tens of thousands. Then the formula will slightly change:
$A$1:$C$100 – the writing of this has changed because you’re extending the formula to all of your searched cells whilst the range of search should not change for every next cell (the range becomes fixed). If you don’t fix it, then the range will add one consequent cell to every next row, so eventually, you will not be looking inside of the initial range (that would be an error).
And as you can search in various columns, you can find many characteristics of your values, rearranging them in a table or in a reporting view as you wish.
- Inserting the current date
Sometimes, values in your data sheets may change depending on the date. And if you would like to find the value on a certain day that is dependent on today (for instance, what the value was 3 days ago from today), you shall use the function of inserting today’s value.
For that, print
and Excel will write today’s date (according to the data set in your PC/laptop)
After that, make the formula in this cell look like
to set the date 3 days ago
or you can extract 3 in another cell from that one if you want to keep your today’s date always written. Then you can do the VLOOKUP if you’re about to search for something on that date or do other actions.
Remember that the current date is inserted, so every next day the formula will automatically update with the proper data. If you want to keep some of the days still, enter it as a text.
- Insert random data
Excel can insert random data, which might be used in various calculations and routine checks of the correctness of other formulas. There are two ways of inserting it (with the “=” sign at the beginning of a line):
This function does not require anything in the brackets and it will return any random number from 0 to 1;
- N is the number from
- M is the number to.
It will return any number in the range you rigidly define.
It is possible to supplement these functions with any rounders (like Roundup or Round down), logical checks, and other elements to your liking. If you write any values that you want to be randomly picked by the system, then using the VLOOKUP with 1 instead of 0 in the formula will allow you to create a random value generator.
Consider that the random numbers given in this way will automatically change after anything on the sheet is changed.