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

Bill Jelen is the author / co-author of
Excel Dynamic Arrays Straight to the Point

The new Dynamic Array Functions are just one side-effect of an effort to completely rewrite the Calculation Engine in Excel. Joe McDaid and the rest of the CALC team have the laid the groundwork for all future functions in Excel. Yes, the first crop of SORT, SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY are awesome and powerful, but they are just the first of many new functions that will come to Office 365 over the coming years.