Referencing Another Sheet - VLOOKUP vs INDEX vs ??

Bessie Koch

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)

Genevieve P.

Hi @Bessie Koch

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

Bessie Koch

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

Genevieve P.

Hi @Bessie Koch

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

Bessie Koch

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

Genevieve P.

Hi @Bessie Koch

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

Bessie Koch

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

Bessie Koch

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