Vlookup from another sheet where data is not in left to right order

Nancy Heater

Hello!

I am trying to pull data into a Smartsheet from another VERY large Smartsheet. The data I am looking up is to the right of the data I want to populate via a VLOOKUP, which is why I believe my formula isn't working.

Example:

On my new sheet, my search value is located in column 3, which would be the Purchase Order Numbers.

From the VERY large sheet, the lookup table range has the search value (the PO Numbers) in column 5 and the value I need to return (the Sales Order Number) is in column 2.

I know the criteria for a VLOOKUP is that the "lookup table" needs to have search value in the leftmost column, but unfortunately, I don't have that option in this situation. Is there any working around this?

Thanks in advance for the assistance.

Andrée Starå

Hi @Nancy Heater

I'd recommend using a combination of INDEX/MATCH instead.

Would that work?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Nancy Heater

It may, but I have not used that combination before.

Here are my test sheets so you can see what I'm trying to accomplish (these sheets just contain dummy data I use to try and figure formulas out with):

Test Sheet A is the "new" sheet I'm trying to pull Sales Order Numbers in to. https://app.smartsheet.com/b/publish?EQBCT=2a8f00cc49eb4021916f06af91e28095

Test Sheet B is the "existing" sheet I'm trying to pull the Sales Order Numbers from, referencing columns in Yellow. https://app.smartsheet.com/b/publish?EQBCT=f536a92b749d44058104f6545c01dfe7

Thanks!

Aravind GP

Hi Nancy,

The Match function helps to find the row number of a given PO Number in Test Sheet B. If you just use "=MATCH([PO Number]@row, reference Test Sheet B and select the column called Purchase Order Numbers, 0". This will fetch the row number at which this PO# can be found.

You can use the Index function to to find the Sales Order Number of a specific row in Test Sheet B. The index function will be "=INDEX(reference Test Sheet B and select the Sales Order Column, a row number) to pull value from a particular row.

Since you would like to find the SO# for the appropriate PO#, you will use both functions together and it will look something like this. "=INDEX({SalesOrderNumbers}, MATCH([PO Number]@row, {PurchaseOrderNumbers}, 0))".

I have named the reference of Sales Order Number column in Test Sheet B as "SalesOrderNumbers" and Purchase Order Numebrs column in Test Sheet B as "PurchaseOrderNumbers".

Hope this helps.

Thanks,

Aravind.

Nancy Heater

@Aravind GP - thanks for the help, but I have to admit that you totally lost me on this one!

Are you able to show me on the Test Sheets I have linked, how to do what you have proposed?

Thanks!

Nancy Heater

@Andrée Starå - I actually think I figured this out using another post that you had commented on in June of 2020!

Still testing to confirm it works for what I'm needing.

Sandra Guzman

Hi @Nancy Heater,

The Test document has a formula in there that is working. Not sure if that was your doing but you are on the right track!

=INDEX({Test Sheet B: Sales Order Number}, MATCH([PO Number]@row, {Test Sheet B: Purchase Order Numbers}, 0))

I love INDEX/MATCH!

All the best,

Sandra

Nancy Heater

Thanks, @Sandra Guzman !

It is working well on my Test sheets, but I am running into some issues with this and Vlookup on my actual data sheets.

When I try and either drag down the formula and/or make it a Column Formula, its duplicating the result data. So I am seeing the same Sales Order Numbers repeat, even though the PO data they are referencing has unique Sales Orders associated with it.

Not sure if this is just something wonky going on with Smartsheet at the moment, as my Vlookups are doing the same thing too.

I'll come back to this tomorrow to see if anything has changed. Thanks!

Sandra Guzman

@Nancy Heater - I agree sometimes looking at things with a fresh set of eyes helps alot!

Your formula is looking at a blank cell and there pulling through the top most row that matches. So that is why when you drag the formula you are seeing the sales order populate multiple times.

If you look at your source sheet.... 1742884 is the top most Sales Order Number that is listed next to a blank field.

It seems to me that if you have your Sales Order number first and need to wait on the Purchase Order, maybe you just need to flip the formula so that can enter in the Sales Order Number and copy the formula. Once the PO number is entered in the source sheet then it would automatically populate.

Food for your early morning thoughts....

Sandra

Andrée Starå

@Nancy Heater

Excellent!

Glad to hear that it was helpful!

✅Remember! Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Andrée Starå

@Sandra Guzman Nice catch!

@Nancy Heater

You could add the IF function to check if the cell is blank and then show blank.

Make sense?

Nancy Heater

I think I have it all figure out now. Discovering the Index / Match formula, and how @Andrée Starå wrote it out made it very easy to understand!

=INDEX({ColumnWithTheValueYouWantToShow}; MATCH(CellThatHaveTheValueToMatch@row; 
{ColumnWithTheValueToMatchAgainsTheCell}; 0))

Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.

Thank you all for your help!!!!