Help!!! I have no clue where to start.

[kiggycoo]

I am stumped! I am trying to figure out a formula and it is jut not working. I am working with SKU's and UPC's. Where I work I am dealing with product that has either a SKU or a UPC on it, and I am currently in the process of having to verify where product is in the warehouse. What I am needing is when my associates scan the label in column 1 the opposite information comes up in column 2. For example if they scan a SKU in column 1 the corresponding UPC will show up in column 2 and if they scan an UPC in Column 1 the SKU will show up in column 2. Is there any way for this to actually work?

 

[shg]

=IFERROR(INDEX(SKUColumn, MATCH(A2, UPCColumn, 0)), INDEX(UPCColumn, MATCH(A2, SKUColumn, 0)))

 

[kiggycoo]

How do I get it to pull the information needed from another sheet in the same workbook? or will this still work in this form?

=IFERROR(INDEX(SKUColumn, MATCH(A2, UPCColumn, 0)), INDEX(UPCColumn, MATCH(A2, SKUColumn, 0)))

 

[kiggycoo]

Here I mad a google doc of the sheet that i am working on.

https://docs.google.com/spreadsheets/d/1mOHj2WKT1jhhTYy5ZznfHT91PSO5vWYLHjpzPOIUQXc/edit?usp=sharing

Maybe this will help.

 

[shg]

This is an Excel forum, and you can upload your workbook here.

EDIT: Wrong forum. Hold on.

 

[shg]

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][td="bgcolor:#C0C0C0"]

F​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Item SKU​

[/td][td="bgcolor:#F3F3F3"]

UPC​

[/td][td][/td][td="bgcolor:#F3F3F3"]

Input​

[/td][td="bgcolor:#F3F3F3"]

Output​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]ZZTSMP0057[/td][td]ZZTSMP005700[/td][td][/td][td]C43ZZTS110003[/td][td="bgcolor:#E5E5E5"]SKU: ZSMP004800[/td][td]E2: =IFERROR("UPC: " & INDEX($A$1:$A$69776, MATCH(D2, $B$2:$B$64892, 0)),
"SKU: " & INDEX($B$2:$B$64892, MATCH(D2, $A$1:$A$69776, 0)))[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]ZZTSMP0056[/td][td]ZZTSMP005600[/td][td][/td][td]ZZTSMP004700[/td][td="bgcolor:#E5E5E5"]UPC: ZZTSMP0048[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]ZZTSMP0055[/td][td]ZZTSMP005500[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]ZZTSMP0054[/td][td]ZZTSMP005400[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]ZZTSMP0053[/td][td]ZZTSMP005300[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]ZZTSMP0052[/td][td]ZZTSMP005200[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]ZZTSMP0051[/td][td]ZZTSMP005100[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]ZZTSMP0050[/td][td]ZZTSMP005000[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]ZZTSMP0049[/td][td]ZZTSMP004900[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td]ZZTSMP0048[/td][td]ZZTSMP004800[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td="bgcolor:#FFFF00"]ZZTSMP0047[/td][td="bgcolor:#FFFF00"]ZZTSMP004700[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

13​

[/td][td]C43ZZTS110004[/td][td]ZZTS110004[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

14​

[/td][td="bgcolor:#FFFF00"]C43ZZTS110003[/td][td="bgcolor:#FFFF00"]ZZTS110003[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

15​

[/td][td]C43ZZSMP0048[/td][td]ZSMP004800[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

16​

[/td][td]CX1ZZSMP0047[/td][td]ZSMP004700[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

 

[Alphonse68]

Hi.
In Main Sheet, Column 2 (B3 on down) the formula should be (copied, pasted, and modified SHG's formula) the following:

=IFERROR("UPC: " & INDEX("SKU/UPC list"!$A$1:$A$69776, MATCH(D2, "SKU/UPC list"!$B$2:$B$64892, 0)),"SKU: " & INDEX("SKU/UPC list"!$B$1:$B$64892, MATCH(D2, "SKU/UPC list"!$A$2:$A$69776, 0)))

The modification I made was to allow you to grab the data from another sheet in the same workbook, in this case, SKU/UPC list; since it has a special character (/) it needs to be enclosed in double quotes, and it is followed by an exclamation point, as "SKU/UPC list"!

Now, if you only need the numbers in Column 2 without the labels UPC: and SKU: in front of them, remove the "UPC: " & and the "SKU: " & from the formula.

CHeers