Findv in the Excel data in the left columns
This means that in a table we could only search by reference to the same column or the next column to the right. But what happens when the data we want to search is to the left or backwards?
For example, let's look at the following case. We have a table showing coronavirus information by country and we want to know the name of the country that has recorded the most new cases
We will first look in relation to column B of our example and identify who is the country with the most cases that day and with respect to that which brings us the name of the country. That is, we have to search from column B as the search column, and that column A returns as the result value. So, let's see how we can do this with search in excel.
There is one method which is the most used to do a you searched backwards and use the formula index and match. This time we will explain how to apply ours findv function in excel with a new method. It's a bit longer step but much simpler, since it doesn't use complex formulas nor is it that long.
You may also be interested in: How to concatenate a list of data in Excel
How to use the Search function in Excel
We will solve our exercise of search in excel raised earlier with country cases.
The first thing is to know which country has had the most cases that day. So, with the Max formula we will identify the largest number, then apply it to the entire column B
With this Excel function we can know that on this day the highest number of registered cases was 29,744. But we still don't know which country has recorded that number of cases.
Now we need to identify the country to which those 29,744 cases refer, for which we will do several things:
First, identify in which row or row number is the country that the 29,744 cases correspond to and for this we will use the coincidence formula, where our searched value is the 29,744 and the searched matrix will create the data of our column B and it matches the type 0 to be exact.
That is, we would have something like this: =MATCH(F2,B:B,0)
It gives us 110, which means that the country with the most cases is in row 110 of our table. Now we have identified the row and we are going to use a formula called address in order to obtain an absolute reference of cell 110 in the column where we want to identify our country.
Therefore, applying it, we would have something like the following: =ADDRESS(E2,1)
Now simply, how do we materialize the $A$110 reference so that it runs as an Excel formula. To perform a formula reference that is written and right now it is text, we can perform it as a calculation using the INDIRECT formula and we select the reference $A$110 that we just found and basically we have our country with the highest number of cases sign in.
visual search function
This formula search in excel, we could have done this in a single formula by nesting it. We just did it this way, so you guys can see each calculation that each one refers to.
If we wanted to find the three countries with the most cases, we could do it as follows:
We already have the maximum and we will find the second, for this we use the formula =K.ESIMO.MAYOR(B:B;2) where the matrix is column B and the K or index number is going to be 2, which would be the second bigger and now we do the same for the third data and change the index to 3.
Now let's replicate each of the formulas downwards to have our three countries with the most cases for this day, making a search in excel back or left with a new method.
We hope so Excel tutorial It can help you and you can apply it in each of your fields, as we told you at the beginning this is a new method that exists to make a buscarv in excel backwards.
There is already a better known one which is to use the index and match nested function. But this, it seems to us from the calculations and not using such complex formulas, is much easier.