Look up or Index or ????

k03074

Board Regular
Joined
Mar 28, 2013
Messages
57
Hello,

I have this spreadsheet:

PART_NUMINV_LOCQTY_OH
00000EXPMAIN-646
0000CARTMAIN-223
0CC00071MAIN-135
0CC00091MAIN-18
2408MAIN-973
28847FG-224
A1014WHSE3143
A1015WHSE2964
A1018WHSE444
A1019WHSE1094
A1029SERVICE
A1103WHSE
A1113FG1772
A1113STPPP
A1113STPPROTEC6192
A1115FG1442
A1117FG304
A1118FG2160
A1118REWORK142
A1120FG
A1120WHSE1500
A1121FG13
A1121REWORK150
A1140FG7110
A1153FG
A1153ABMAIN
A1153ADFG590
A1155FG220
A1155WHSE
A1156FG59
A1162FG92
A1163FG657
A1163WHSE982

<tbody>
</tbody><colgroup><col><col><col></colgroup>

And I need to pick out location for each part and put in the quality, it no quality then I need a zero.

Here is the other spreadsheet I have:

PART_NUMMAINFGWIP
A11131772
A1113STP0
A11151442
A11182160
A11407110
A1140STP0
A1153AB0
A1153AC0
A1153AD590
A11860
A1193STP0
A1193TOG0
A1193WELD0
A1194583
A1252AB0
A1252AC6091
A1293STP0
A1325AA2916
A1325AB0
A1326AA621
A1327AA0
A1327STPAA3670
A2100STPAC0
A2100STPAD0
A2101AC0
A2101AD0
A2101AE1107
B10517901
B1069AB0
B1071AB0
B1072AB0
B1074AB0

<tbody>
</tbody><colgroup><col><col span="3"></colgroup>

I know that I have my totals work in MAIN column. Not sure if use INDEX or VLOOKUP.

So any help would be appreciate.

Thank you,
Gerald
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
You could use VLOOKUP or INDEX/MATCH.

Does the below work for you?

Sheet 2, Cell B2:
Code:
=IF(VLOOKUP(A2,Sheet1!$A$2:$C$34,3,0)="",0,VLOOKUP(A2,Sheet1!$A$2:$C$34,3,0))
 
Upvote 0
Okay, I need it to match the part number, and check to see if its Inv_loc and it so then display the Qty_OH else display 0.
 
Upvote 0
If you have Excel 2010 or later you can use SUMIFS.

Excel Workbook
ABCD
1PART_NUMMAINFGWIP
2A1113017720
3A1113STP000
4A1115014420
5A1118021600
6A1140071100
7A1140STP000
8A1153AB000
9A1153AC000
10A1153AD05900
11A1186000
12A1193STP000
13A1193TOG000
14A1193WELD000
15A1194000
16A1252AB000
17A1252AC000
18A1293STP000
19A1325AA000
20A1325AB000
21A1326AA000
22A1327AA000
23A1327STPAA000
24A2100STPAC000
25A2100STPAD000
26A2101AC000
27A2101AD000
28A2101AE000
29B1051000
30B1069AB000
31B1071AB000
32B1072AB000
33B1074AB000
Sheet2
Excel Workbook
ABC
1PART_NUMINV_LOCQTY_OH
200000EXPMAIN-646
30000CARTMAIN-223
40CC00071MAIN-135
50CC00091MAIN-18
62408MAIN-973
728847FG-224
8A1014WHSE3143
9A1015WHSE2964
10A1018WHSE444
11A1019WHSE1094
12A1029SERVICE*
13A1103WHSE*
14A1113FG1772
15A1113STPPP*
16A1113STPPROTEC6192
17A1115FG1442
18A1117FG304
19A1118FG2160
20A1118REWORK142
21A1120FG*
22A1120WHSE1500
23A1121FG13
24A1121REWORK150
25A1140FG7110
26A1153FG*
27A1153ABMAIN*
28A1153ADFG590
29A1155FG220
30A1155WHSE*
31A1156FG59
32A1162FG92
33A1163FG657
34A1163WHSE982
Sheet1
 
Upvote 0
Thank you very much. This worked like a charm. I learn something new today, never knew about the SUMIFS statement.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

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