# vlookup alternative for array of data?

#### haleysmithc

##### New Member
I am trying to get a total of units sold for a particular UPC. The units are not summed up before they reach me, so vlookup does not work here--first I need to get the total of the units, and then I need to input that total. I usually use a pivot table for this, but recently I've run into issues and started to wonder if it was possible to do this with a formula.

This is where I am trying to total and gather the info:

UPC #Actual Units Sold
 786936856934

<tbody>
</tbody>
 786936858136

<tbody>
</tbody>
 786936859119

<tbody>
</tbody>
 786936856972

<tbody>
</tbody>
 786936858990

<tbody>
</tbody>
 786936859300

<tbody>
</tbody>

<tbody>
</tbody>

But the info from the table where I am pulling from looks like this:

 UPC # Actual Units Sold 786936856934 506 786936856934 37 786936856934 189 786936858136 22 786936858136 806 786936859119 224

<tbody>
</tbody>
Etc, etc... (the current table I'm working with has about ~4500 rows)

I assume some form of array would accomplish this, but after researching it I am not sure I am able to find the one I need. Any help is appreciated!

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Welcome to the Board!

There is a built-in function to do just what you want:

ABCDEF
1UPC #Actual Units SoldUPC #Actual Units Sold
2786936856934732786936856934506
378693685813682878693685693437
4786936859119224786936856934189
5786936856972078693685813622
67869368589900786936858136806
77869368593000786936859119224

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet12

Worksheet Formulas
CellFormula
B2=SUMIF(D:D,A2,E:E)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

I assume your numbers are stored as text, otherwise Excel will convert them to scientific notation, and you'll lose the significant digits.

Hope this helps!

Wow, I didn't even know about that! I had no idea the solution would be so simple. Thank you

You're welcome!

Replies
4
Views
420
Replies
3
Views
379
Replies
1
Views
957
Replies
17
Views
887
Replies
108
Views
2K

Threads
1,203,524
Messages
6,055,904
Members
444,832
Latest member
bgunnett8

### 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

### 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