MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Lookup Row & Sheet


November 13, 2017 - by Bill Jelen

Lookup Row & Sheet

How to write an Excel formula that will lookup a value on a different sheet based on which product is selected. How to pull data from a different worksheet for each product.

Watch Video

  • Rhonda from Cincinnati: How to look up both row and worksheet?
  • Use the Date column to figure out which sheet to use
  • Step 1: Build a regular VLOOKUP and use FORMULATEXT to see what the reference should look like
  • Step 2: Use Concatenation and the TEXT function to build a reference that looks like the table array reference in the formula
  • Step 3: Build your VLOOKUP, but for the table array, use INDIRECT( results from step 2)
  • Step 4: Copy the formula from Step 2 (without the equals sign) and paste in to the formula from step 3

Auto-Generated Transcript

  • Learn Excel from MrExcel Podcast
  • episode 2173 look up the sheet and row
  • hey welcome back to the MrExcel
  • NetCast I'm Bill Jelen I was in
  • Cincinnati last week in Rhonda and
  • Cincinnati had this great question
  • Rhonda needs to look up this product but
  • the lookup table is different depending
  • on which month it is see we have
  • different look-up tables here for
  • January through April and presumably for
  • the other months as well all right
  • somebody who's in direct to solve this
  • but before I do in direct I always find
  • it easier just to do a straight vlookup
  • so we can see what the form is going to
  • look like so we're looking up a1 in this
  • table on January and we want the seventh
  • column comma false all of you lookups
  • and in false
  • alright and well that's the right answer
  • what I'm really interested in is getting
  • the formula text of that so it shows me
  • what the formulas going to look like and
  • the whole trick here is I'm trying to
  • build a helper column that's gonna look
  • exactly like this reference right so
  • this part just that part right there
  • alright so this helper column has to
  • look like that thing looks and the first
  • thing I want to do is when use the text
  • function of the date the text function
  • of the date to get mmm space why why why
  • why
  • so mmm space why why why like that which
  • should return for each of the cells what
  • month we're looking up now I need to
  • wrap that in apostrophes if there hadn't
  • been a space name in there I wouldn't
  • need the apostle reason but I do so
  • we're gonna concatenate up front the
  • apostrophe so that's a quote ' quote
  • ampersand and then over here another
  • quote apostrophe and exclamation point a
  • 1 : g13
  • [Music]
  • closing quote
  • ampersand there all right so now what
  • we've successfully done over here in the
  • helper column is we built something that
  • looks exactly like the table array in
  • the vlookup all right so our answer then
  • it's going to be equal vlookup of this
  • cell a 2 comma and then when we get to
  • the table array we're gonna use the
  • indirect and direct is this cool
  • function that says hey here's a cell
  • that looks like a cell reference and I
  • want you to go to F to take the thing
  • that looks like a cell reference and
  • then use whatever is in that cell
  • reference as the answer comma 7 comma
  • false like that alright so now on the
  • fly we're choosing a different lookup
  • table and returning the values depending
  • on whether it's april or or what alright
  • so let's take this 424 I'll change it to
  • 217 2018 like that and we should see
  • that 403 changed at 203 perfect it is
  • working all right now we don't need
  • these two columns here of course and
  • really if you think about it we don't
  • need this whole column we could take
  • that whole thing except for the equal
  • sign ctrl C to copy it and then where we
  • have D to just paste like that perfect
  • double click to shoot that down and get
  • rid of this there is our answer
  • all right we'll use a formula text here
  • just to take a look at that final answer
  • I have to tell you if I had to build
  • that formula just from scratch I
  • wouldn't do it I wouldn't be able to do
  • it I would screw it up for sure that's
  • why I always build it in steps I figure
  • out what the formulas gonna look like
  • and then concatenate the helper column
  • that will be used inside the indirect
  • and then finally maybe here at the end
  • put everything back together
  • hey many tips like this tip in the book
  • power excel with MrExcel this is the
  • 2017 edition with 617 excel mystery
  • solved click that I on the top right
  • hand corner for more information all
  • right wrap up from this episode Rhonda
  • from Cincinnati how to look up both the
  • row and the worksheet I use the date
  • column to figure out which sheet to use
  • so I build a regular vlookup and use
  • formula text to see
  • but the reference should look like and
  • then build something that looks like
  • that reference using the text function
  • to convert the date to a month in year
  • use concatenation to build something
  • that looks like the reference and then
  • when you build your vlookup for the
  • second argument the table array use
  • indirect and then point to the results
  • from step two and then the optional
  • fourth step there copy the formula from
  • step two without the equal sign and
  • paste it into the formula from Step
  • three so you end up with a single
  • formula well I want to thank Rhonda for
  • showing up my seminar in Cincinnati no I
  • thank you for stopping by we'll see you
  • next time for another net cast from mr.
  • Excel

Download File

Download the sample file here: Podcast2173.xlsm

Title Photo: JuliaBoldt / Pixabay