# Referencing Another Sheet - VLOOKUP vs INDEX vs ??

Options

Hello,

I need to populate a cell with a number that is in another sheet based on an independent UID found in a different column that will be on both sheets. Not sure which direction to go on this --

For each line form-generated line, D. needs to look at C., find/match the data to A., then populate D with B when:

A. Production Sheet 1/Column 1: SKU Tag # (UID)

B. Production Sheet 1/Column 2: Starting Qty (Manually Inputted by Operator)

C. Packaging Sheet 2/Column 1: SKU Tag # (UID)

D. Packaging Sheet 2/Column 2: Starting Qty (Formula Column)

Tags:

• Employee
Options

It sounds like an INDEX(MATCH will work for you!

For example, you could put this in Sheet 2 Column 2:

=INDEX({Sheet 1 Column 2}, MATCH([Column 1]@row, {Sheet 1 Column 1}, 0))

The first {Range} is the column that has the data you want to bring back from the other sheet. Then in the MATCH function you first list the value in the current sheet, then search for it in the second {Range} from your other sheet.

Cheers,

Genevieve

Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now

Options

This worked beautifully! Thank you so much!

A follow-up question...is there a way to embed an IF/THEN to an INDEX/MATCH? I got "#NO MATCH" on some and realized that there are two sheets feeding into the one.

A. Production Sheet 1/Column 1: SKU Tag # (UID)

B. Production Sheet 1/Column 2: Ending Qty (Manually Inputted by Operator)

C. Production Sheet 2/Column 1: SKU Tag # (UID)

D. Production Sheet 2/Column 2: Ending Qty (Manually Inputted by Operator)

E. Packaging Sheet 3/Column 1: Fruit Name

F. Packaging Sheet 3/Column 2: SKU Tag # (UID)

G. Packaging Sheet 3/Column 3: Starting Qty (Formula Column)

If E = "Bananas", then F needs to find a match in A and populate G with B

If E = "Apples", then F needs to find a match in C and populate G with D

• Employee
Options

It sounds like an INDEX(MATCH will work for you!

For example, you could put this in Sheet 2 Column 2:

=INDEX({Sheet 1 Column 2}, MATCH([Column 1]@row, {Sheet 1 Column 1}, 0))

The first {Range} is the column that has the data you want to bring back from the other sheet. Then in the MATCH function you first list the value in the current sheet, then search for it in the second {Range} from your other sheet.

Cheers,

Genevieve

Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now

Options

This worked beautifully! Thank you so much!

A follow-up question...is there a way to embed an IF/THEN to an INDEX/MATCH? I got "#NO MATCH" on some and realized that there are two sheets feeding into the one.

A. Production Sheet 1/Column 1: SKU Tag # (UID)

B. Production Sheet 1/Column 2: Ending Qty (Manually Inputted by Operator)

C. Production Sheet 2/Column 1: SKU Tag # (UID)

D. Production Sheet 2/Column 2: Ending Qty (Manually Inputted by Operator)

E. Packaging Sheet 3/Column 1: Fruit Name

F. Packaging Sheet 3/Column 2: SKU Tag # (UID)

G. Packaging Sheet 3/Column 3: Starting Qty (Formula Column)

If E = "Bananas", then F needs to find a match in A and populate G with B

If E = "Apples", then F needs to find a match in C and populate G with D

• Employee
Options

Yes!

We can use an IFERROR function to say that "If there's an error, look into sheet 2 instead".

=IFERROR(First Formula, Second Formula)

Or:

=IFERROR(INDEX({Sheet 1 Column 2}, MATCH([Column 1]@row, {Sheet 1 Column 1}, 0)), INDEX({Sheet 2 Column 2}, MATCH([Column 1]@row, {Sheet 2 Column 1}, 0)))

See: IFERROR Function

Cheers,

Genevieve

Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now

• Options

Thank you so much!! Working on testing this :)

• Options

Tested this and it works great! Thank you very much!!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!