Situation
We know that VLOOKUP formula is useful to fetch the first matching item from a list. So what would you do if you need 2nd (or 3rd etc.) matching item from a list?
For eg. If you have below data, and you want to find out how much sales John made 2nd time, then VLOOKUP formula becomes quite useless. Or is it?!?
Data:
Solution
A simple solution to this problem would be sorting our data on sales person’s name. That way all Johns would line up one beneath another. And we just have to find the first John’s position and add 1 to it to get to 2nd occurrence. Like this =MATCH("John", C5:C17, 0) + 1
But sorting is not an option all the time. So there should be a better way to do this?
Well, there is. We just add a helper column before the sales person name and fill it with sales-person’s name & occurrence. (see the below data table).
For this we can use COUNTIF() Formula, like this: =C5&COUNTIF($C$5:C5,C5). Notice the $C$5:C5?, well the mix of absolute & relative references does the trick here and gets John1, John2… etc.
Now, to lookup 2nd occurance of John, all we do is, simply write =VLOOKUP("John2",...) and we are done.
Sample File
Download Example File – Getting the 2nd matching item from a list using VLOOKUP formula
The file includes few examples on how to fetch 2nd, 3rd etc. matches using lookup formulas. It also has some interesting (and challenging) home work for you. Download & play with it.
Similar Tips
- Excel SUMPRODUCT Formula – What is it, how to use it & examples
- Excel VLOOKUP Formula – What is it, how to use it & examples
- Excel SUMIF and COUNTIF Formulas – What are they, how to use & examples
3 Responses to “Filter one table if the value is in another table (Formula Trick)”
What about the opposite? I want a list of products without sales or customers with no orders. So I would exclude the ones that are on the other table.
Good question. You can check for the =0 as countifs result. for example,
=FILTER(orders, COUNTIFS(products, orders[Product])=0)
should work in this case.
PS: I have added this example to the article now.
Hi there!
Could i check if there was a way to return certain fields of the table only?
so based off your example above, i would like to continue to use the 'Products" table as a way to filter out items from my "Orders" table, but only want to show maybe only the "Product" and "Order Value" fields, rather than all 5 fields (sales person, customer, product, date, order value).