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

kiggycoo

New Member
Joined
Sep 19, 2017
Messages
5
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?
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
=IFERROR(INDEX(SKUColumn, MATCH(A2, UPCColumn, 0)), INDEX(UPCColumn, MATCH(A2, SKUColumn, 0)))
 
Upvote 0
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)))
 
Upvote 0
This is an Excel forum, and you can upload your workbook here.

EDIT: Wrong forum. Hold on.
 
Last edited:
Upvote 0
A​
B​
C​
D​
E​
F​
1​
Item SKU
UPC
Input
Output
2​
ZZTSMP0057ZZTSMP005700C43ZZTS110003SKU: ZSMP004800E2: =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)))
3​
ZZTSMP0056ZZTSMP005600ZZTSMP004700UPC: ZZTSMP0048
4​
ZZTSMP0055ZZTSMP005500
5​
ZZTSMP0054ZZTSMP005400
6​
ZZTSMP0053ZZTSMP005300
7​
ZZTSMP0052ZZTSMP005200
8​
ZZTSMP0051ZZTSMP005100
9​
ZZTSMP0050ZZTSMP005000
10​
ZZTSMP0049ZZTSMP004900
11​
ZZTSMP0048ZZTSMP004800
12​
ZZTSMP0047ZZTSMP004700
13​
C43ZZTS110004ZZTS110004
14​
C43ZZTS110003ZZTS110003
15​
C43ZZSMP0048ZSMP004800
16​
CX1ZZSMP0047ZSMP004700
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top