Don't use HLOOKUP...just turn the table back to a vertical table using the two methods shown in Episode 1125.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Basically, you start out with massive amounts of data, and say “How we're going to analyze this?” Well, let's fire up a Pivot table and see if we can solve this problem!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Well, last week we did VLOOKUP, this week HLOOKUP!
Are you kidding me?
I've never done an HLOOKUP in my life!
H, horizontal means that someone built the table the wrong way, they built it going across the columns instead of down the rows.
You know what I do when I have this?
Just choose this table, copy Ctrl+C, and then usually Edit, Paste, Special, Transpose, although now in Excel 2007, Home, Paste, Transpose.
And now we have a table going the right way, very easy to use VLOOKUP to solve that problem, so there you have it.
Hey, let me give you a bonus tip!
Now this creates a static copy of that table, what if this table is actually formulas that are changing, and we want to grab a live view of that table?
Well, here's what we do, we say =INDEX of this range here, so Ctrl+Shift+Right, press F4.
And which row we want, oh that's easy, it's row 1, there's only one row, and then which column we want, we say we want the ROW(A1), being the world of geekiest way to write the number 1.
Enter that, copy it down, and you see that that gets the ROW(A2), ROW(A3), ROW(A4), it takes that data and turns it sideways.
So now if we would come here and enter 5%, you see that there, BAM, it changes.
So two different ways: Edit, Paste, Special, Transpose, take the table and turn it sideways; or this cool formula, that will create a live view at that table, and now over here we can just do VLOOKUP!
There you have it, thanks for stopping by, we'll see you next time for another netcast from MrExcel!
Well thanks for stopping by, we'll see you next time for another netcast from MrExcel!
Basically, you start out with massive amounts of data, and say “How we're going to analyze this?” Well, let's fire up a Pivot table and see if we can solve this problem!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Well, last week we did VLOOKUP, this week HLOOKUP!
Are you kidding me?
I've never done an HLOOKUP in my life!
H, horizontal means that someone built the table the wrong way, they built it going across the columns instead of down the rows.
You know what I do when I have this?
Just choose this table, copy Ctrl+C, and then usually Edit, Paste, Special, Transpose, although now in Excel 2007, Home, Paste, Transpose.
And now we have a table going the right way, very easy to use VLOOKUP to solve that problem, so there you have it.
Hey, let me give you a bonus tip!
Now this creates a static copy of that table, what if this table is actually formulas that are changing, and we want to grab a live view of that table?
Well, here's what we do, we say =INDEX of this range here, so Ctrl+Shift+Right, press F4.
And which row we want, oh that's easy, it's row 1, there's only one row, and then which column we want, we say we want the ROW(A1), being the world of geekiest way to write the number 1.
Enter that, copy it down, and you see that that gets the ROW(A2), ROW(A3), ROW(A4), it takes that data and turns it sideways.
So now if we would come here and enter 5%, you see that there, BAM, it changes.
So two different ways: Edit, Paste, Special, Transpose, take the table and turn it sideways; or this cool formula, that will create a live view at that table, and now over here we can just do VLOOKUP!
There you have it, thanks for stopping by, we'll see you next time for another netcast from MrExcel!
Well thanks for stopping by, we'll see you next time for another netcast from MrExcel!
