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.
8 Responses to “Create a Combination Chart, Add Secondary Axis in Excel [15 Second Tutorial]”
[...] Select the “daily completed” column and add it to the burn down chart. Once added, change the chart type for this series to bar chart (read how you can combine 2 different chart types in one) [...]
[...] set the height series to be plotted on secondary axis. Learn more about combining 2 chart types and adding secondary axis in [...]
[...] Excel Combination Charts – What are they? [...]
[...] To show the years, I have used another dummy series and plotted it on secondary axis (related: how to add secondary axis?) [...]
Thanks for this one!
[...] Choisissez la colonne « Daily Completed » et ajoutez-la au graphique. Une fois ajoutée, changez le type de graphique pour cette série à histogramme (lisez comment combiner 2 types de graphiques en un : combine 2 different chart types in one) [...]
How do i create a chart that has negative numbers on axis x and y and plot them correctly? I cannot seem to understand how to do this, please help.
Thanks.
Nat
You can also plot 2 or more Y axes in Excel using EZplot or Multy_Y from Office Expander.com
There is a demo version to try.
Cheers.