Pulling in values from another sheet

Matthijs Schoots

I have a column with a Contract Number and to the right I want to auto-fill two associated columns, Vendor and Contract Manager linked to this same contract number from another sheet.

I have tried all manner of VLOOK-UP and INDEX/MATCH combinations which I know from Excel but no success. Is there a basic formula to build on for this please?

The source sheet for the Vendor entries has two indent levels. Don't know if that matters.

SmartLew

Hi Matthijs,

An index match should work fine for this. What error are you getting?

Follow this formula

=index(Vendor column in source sheet),match(contract number@row,contract number column in source),0)

Paul McGuinness

Hi @Matthijs Schoots

This is possible, please see example below:

Although this example is on the same sheet the formula will work cross sheet, you just need to replace the column ranges with sheet references.

Blue would be your source sheet, green is the destination sheet.

Formula in green Vendor column: =INDEX([Vendor.]:[Vendor.], MATCH([Contract no]@row, [Contract no.]:[Contract no.], 0))

Formula in green Contract Manager column:

=INDEX([Contract manager.]:[Contract manager.], MATCH([Contract no]@row, [Contract no.]:[Contract no.], 0))

references in this format need to be updated to reference the first sheet.

Hope this helps

Thanks

Paul

SmartLew

Hi Matthijs,

An index match should work fine for this. What error are you getting?

Follow this formula

=index(Vendor column in source sheet),match(contract number@row,contract number column in source),0)

itsnotbroken

Hi Matthijs,

The syntax with INDEX, MATCH is tricky.

Most people think it's INDEX(MATCH) but it's not

IT's really INDEX,MATCH

This is the syntax from the INDEX pop-up:

=INDEX(range, row_index, column_index)

We're not using the column index. Only the row index.

The MATCH is the row index. It looks for the contract number & sends back the row number

I just did a test with 2 sheets and here's the basic formula:

=INDEX({Sheet2-Vendor}, MATCH([Contract Number]@row, {Sheet2 Contract}, 0))

Note the curly braces are the range from {Sheet2}. You can't just type them in, you have to create the range from within the INDEX formula pop-up window.

In the example, my cursor is in the [Vendor] column. Note the yellow highlight. Now you need to click the "Edit Reference" hyperlink to make a link to {Sheet 2}

I named the ranges to make our example clearer - {Sheet2-Vendor} and {Sheet2-Contract} and {Sheet2-Manager}

Here's the results with Vendor AND Contract Manager brought back from {Sheet 2}

Note the small blue triangles showing inbound data

Hope this helps,

Mark V

Matthijs Schoots

thank you so much all for your amazing and quick help. I am new to smartsheet and am blown away by the strength of the community here.

SmartLew

https://community.smartsheet.com/discussion/comment/340950#Comment_340950

Great to hear Matthijs. Continue to reach out! Some great people on here.