Situation
So far we have seen what VLOOKUP formula is and how to put it to some nifty uses. Today, we will go one step further and learn how to do 2 Way Lookups.
What is a 2 Way Lookup?
Lookup is when you find a value in one column and get the corresponding element from other columns. 2 Way Lookup is when you lookup value at the intersection corresponding to a given row & column values.
For example, assuming you have data like below, and you want to findout how much sales Joseph made in month of March, you are essentially doing a 2 way lookup.
Data:
Solution
While the problem may seem complicated, the solutions to two way lookups are surprisingly simple. In this post, I will review 4 different ways to write 2 way lookup formulas.
Keep this in mind:
- I use various named ranges in the below examples.
valSalesPersonandvalMonthrefer to the name of sales person & month we are looking for.lstSalesPersonandlstMonthrefer to the list of sales persons (first column) and list of months (first row).tblDatahas the sales figures for everyone.
Technique 1 – Using INDEX & MATCH Formulas
If you know the row number and column number in a given table, you can use INDEX formula to get the element at the intersection. And we can use MATCH formula to find the position of an value in a list. Combining both,
=INDEX(tblData,MATCH(valSalesPerson,lstSalesPerson,0),MATCH(valMonth,lstMonth,0)) is the formula we use to get the sales amount of valSalesPerson for valMonth.
Technique 2 – Using Named Ranges & Intersection (SPACE) Operator
Do you know that you can write =range1 range2 to get the value(s) at the intersection of range1 & range2? That is right, excel has an intersection operator. I will write more about this some other time. In the meanwhile, watch this short video to understand how you can use named ranges & intersection operator to perform 2 way lookups.
However, you need to create named ranges for your data all the time. A simpler alternative is to use Excel 2007 Tables feature so the names are created for you automatically.
Technique 3 – Using SUMPRODUCT Formula
This is an absolute beauty. Thanks to Vipul for teaching me this superb trick.
You can use SUMPRODUCT to get the value at intersection like this: =SUMPRODUCT((lstSalesPerson=valSalesPerson)*(lstMonth=valMonth),tblData)
How does it work? Simple, When you write (lstSalesPerson=valSalesPerson)*(lstMonth=valMonth), SUMPRODUCT generates a lot of zeros and a one at the intersection. When you use tblData as second argument, the result is value at intersection.
Technique 4 – Using VLOOKUP with MATCH Formula
A much simpler and easy to remember alternative. You can simply write =VLOOKUP(valSalesPerson,$B$5:$N$17,MATCH(valMonth,lstMonth,0)+1,FALSE) to fetch the value for a corresponding month.
Review of 2 Way Lookup Techniques
Sample File
Download Example File – 2 Way Lookup Formulas in Excel
Go ahead and download the above file. It contains all the examples. Play with them to learn the 2 way lookup formulas better.
PS: Also download this beautiful example file that Matias has kindly shared with me. It shows how to use INDIRECT formula along with Excel Tables to do 2way lookups.
Special Thanks to
Vipul, Spotpuff, judgepax, Bryan for the tip. (Click on the name to see their tip)
Similar Tips
- Excel SUMPRODUCT Formula – What is it, how to use it & examples
- How to Lookup Values to the Left – Using INDEX + MATCH Formulas
- Range lookup – finding the corresponding range for a value
21 Responses to “How to Filter Odd or Even Rows only? [Quick Tips]”
Infact, instead of using =ISEVEN(B3), how about to use =ISEVEN(ROW())
So it takes away any chance of wrong referencing.
I like Daily Dose of Excel
I like it.
Just a heads up, you do need to have the Analysis ToolPak add-in activated to use the ISEVEN / ISODD functions. An alternative to ISEVEN would be:
=MOD(ROW(),2)=0
rather than use a formula, couldn't you enter "true" in first cell and "false" in the second and drag it down and than filter on true or false.
Just for clarification, is Ashish looking to filter by even or odd Characters or rows?
so many functions to learn!
Nice support by chandoo and team as a helpdesk. Give us more to learn and make us awesome. Always be helpful.......
In case you want to delete instead of filter,
IF your data is in Sheet1 column A
Put this in Sheet2 column A and drag down
=OFFSET(Sheet1!A$1,(ROWS($1:1)-1)*2,,)
(This is to delete even rows)
To delete odd rows :
=OFFSET(Sheet1!A$2,(ROWS($1:1)-1)*2,,)
If your numbered cells did not correspond to rows, the answer would be even simpler:
=MOD([cell address],2), then filter by 0 to see evens or 1 to see odds.
I sometimes do this using an even simpler method. I add a new column called "Sign" and put the value of 1 in the first row, say cell C2 if C1 contains the header. Then in C3 I put the formula =-1 * C2, which I copy and paste into the rest of the rows (so C4 has =-1 * C3 and so forth). Now I can just apply a filter and pick either +1 or -1 to see half the rows.
Another way, which works if I want three possibilities: in C2 I put the value 1, in C3 I put the value 2, in C4 I put the value 3, then in C5 I put the formula =C2 then I copy C5 and paste into all the remaining rows (so C6 gets =C3, C7 gets =C4, etc.). Now I can apply a filter and pick the value 1, 2, or 3 to see a third of the rows.
Extending this approach to more than 3 cases is left as an exercise for the reader.
Another way =MOD(ROW();2). In this case, must to choose betwen 1 and 0.
[...] How to Filter Even or Odd rows only [...]
very different style Odd or Even Rows very easy way to visit this site
http://www.handycss.com/tips/odd-or-even-rows/
Thanks for the tip, it worked like magic, saved having to delete row by row in my database.
Thanks!
Thankssssssssssssssss
Hi Chandoo- First of all thanks for the trick. It helped me a lot. Here I have one more challenge. Having filtered the data based on odd. I want to paste data in another sheet adjacent to it. How can I do that?
For Example-
A 1 odd
B 3 odd
C 4 even
D 6 even
I have fileted the above data for odd and want to copy the "This is odd number" text in adjacent/next sheet here. How can I do that. After doing this my data should look like this
A 1 odd This is odd number
B 3 odd This is odd number
C 4 even
D 6 even
Hi! Could you please help me find a formula to filter by language?
Thank you!
Chandoo SIR,
I HAVE A DATA IN EXCEL ROWS LIKE BELOW IS THERE ANY FORMULA OR A WAY WHERE I CAN INSTRUCT I CAN MAKE CHANGES , MEANS I WANT TO WRITE ONLY , THE FIG IS FRESH, BUT IN BELOW ROW IT WILL AUTOMATICALLY TAKE THE SOME WORDS FROM FIGS AND MAKE IN PLURAL FORM , WHILE USING '' ARE'' LIKE BELOW
The fig is fresh - row 1
Figs are fresh - row 2
The Pomegranate is red - row 3
Pomegranates are red - row 4
=IF(EVEN(A1)=A1,"EVEN - do something","ODD - do something else") with iferron (for blank Cell)