VLookUp and Multiple Rows.

Ann@WeVeel

Hi,

I'm pulling two excel sheets into SmartSheet. Sheet 1 is called the MASTERLIST Sheet 2 is Called the SALESFORM.  The SALESFORM pulls data from the MASTERLIST.

What I want to happen is in the SALESFORM I enter an Item Number that is in the MASTERLIST and data from that row fills in in the specific spots.

Example.



Item Number  /  Item Description / Case Pack / Price / Duty Rate 

These items have other details and formulas between them and dependent on them as well as columns for our Sales Team to fill in based on vendor.

How do I set up my sheet to be able to pull all this detail automatically when I put in the item number into the first column. Column titles are the same between sheets and my above example.

Thank You Much.

Paul Newcome

There are a couple of ways to do this. Are you able to provide some screenshots? how many columns are you trying to auto-fill based on the Item Number being manually entered?

Ann@WeVeel

Paul,

Unfortunately there are too many total to show.  The MASTERLIST has 37 Columns total and SALESFORM is 40. Which is a bit excessive but most of that data works into the SALESFORM in different ways, but I only need 5 columns to pull from MASTERLIST to SALESFORM. Column names are the same on both sheets. I would need to enter the Item Number to pull in the rest of the data.



Item Number  /  Item Description / Case Pack / Price / Case Cube Cu.Ft/ Duty

 

Thanks in advanced.

Ann@WeVeel

Hi,

So I pulled the excel thing so you can see the formula used there blocking some pulled info. You can see by how many columns used in excel, this document is massive. The yellow column is where we fill in the item number and all the other columns pull from the Master Line List document.

Paul Newcome

NOTE: This will be a rather long and detailed post as I am unsure as to your familiarity with formulas and functions within Smartsheet, but it will be relatively straightforward.

.

We will be using two functions. INDEX to pull the data, and MATCH to fill in part of the INDEX function.

 

INDEX is used to display data from a range when given a row number and an optional column number. It looks like this:

=INDEX(data range to pull from, row number, [column number])

with the column number being optional. We won't need it, so we will skip over it once we establish our row number.

.

MATCH will return a number based on where within a grid specific text is found and looks like this:

=MATCH(text to search for, range to search in, [match type])

Even though the match type is optional, we will be using 0 (zero) in this space as that provides for an exact match and gives the most accurate results.

.

So we know we are using INDEX to pull data from a range. I will go in order of each of your columns in the above post, so the first data we want to pull is the Item Description.

We start by typing

=INDEX(

and then click on the "reference another sheet" link in the formula helper box.

You will then select the MASTERLIST sheet and click on the column header for the [Item Description] column. I am going to suggest that you rename the range just to help keep things straight. In this example I will rename it as "MASTERLIST Item Description". Click on "Insert Reference" in the bottom right hand corner, and you will be taken back to your SALESFORM sheet where the range has now been added to your formula.

.

Now we see

=INDEX({MASTERLIST Item Description}

insert a comma, and we are on to the next step.

=INDEX({MASTERLIST Item Description},

.

Now we need to tell the INDEX function which row to pull from within our range. This is where we use the MATCH function to automatically generate a number based on the Item Number in the row that the formula is in on your SALESFORM sheet.

=INDEX({MASTERLIST Item Description}, MATCH($[Item Number]@row, 

tells the MATCH function what we are looking for. To tell it where to look, use the cross sheet referencing steps outlined above and select the [Item Number] column from your MASTERLIST sheet. I'll call this range "MASTERLIST Item Number". We already know we are going to use 0 for the match type, so we'll go ahead and throw that in there as well. That's the end of the MATCH function, so we can close that out, and since we are looking at a single column range for the INDEX function, we can close that out as well which completes the formula.

 

=INDEX({MASTERLIST Item Description}, MATCH($[Item Number]@row, {MASTERLIST Item Number}, 0))

.

 

And there you have it. You are now referencing the Item Number on the SALESFORM sheet to pull the Item Description from the MASTERLIST sheet.

.

Please note the $ before the cell reference of [Item Number]@row. This was intentional. It locks in that column reference which allows for some copy/paste and saves some time with the other 4 formulas.

.

Now all you have to do is copy that first formula and paste it into the remaining columns. Now you have the same formula in all five columns, but the rest of this setup is pretty easy.

 

Simply highlight the first cross sheet reference of {MASTERLIST Item Description}. You should see the link again for "Reference Another Sheet". Click on that, select the column you need from the MASTERLIST sheet, update the range name, click "Insert Reference", and your formula is updated for the new column.

 

Do that for the rest of your formulas updating the first cross sheet reference for the appropriate column on the MASTERLIST sheet, and you're done.

.

Feel free to let me know if there is anything that needs clarified or if you need further assistance.

Ann@WeVeel

OK I'M STILL having trouble.

MY CODE on SALESFORM is

=INDEX({MASTERLIST Range 1},MATCH($[Item Number]1@row{MASTERLIST Range 2})

MasterList Range 1 = Item Number

MasterList Range 2 = Item Description.

With this code in place I am getting the error #UNPARSEABLE Which stays in the box whether or not there are numbers in the Item Numbers category.

I was just figured out how to update the range names so please see screen shots below.

 

 

Ann@WeVeel

OK i was able to make it work by removing the $ and @row.

 

thanks for your help

Paul Newcome

Happy to help!