Here is a question someone asked me in a class recently.
I know how to use VLOOKUP to find a value based on search term. But I have a slight variation to it. I need to extract value below the cell VLOOKUP finds.
This is simpler than it sounds.
We can use INDEX + MATCH formulas to do this.
The syntax is like below:
=INDEX( value column, MATCH (search what, search column, 0) + 1 )
Why it works?
MATCH formula finds the position of what you are searching. By adding 1 to it and extracting the corresponding “values column”, we can get VLOOKUP + 1 value.
Homework for you
If you think finding VLOOKUP+1 is easy then I have a challenge for you.
Find the last match. Lets say in a table you have multiple items matching lookup value. How would you find the last item. Assume what you are finding is in A1, list is in C1:D20 and we want the value in 2nd column.
Go ahead and post your answers in comments section.
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).