EXCEL Vlookup Help

Copper Contributor

I am trying to add multiple values based on zip code. For example, I have a data set based on zip code and want to select multiple zip codes then sum the data to the immediate right of those multiple zip codes.

 

Example:

 AB
155001100
255002200

 

In this scenario I want to include both 85001 and 85002 and return an output of 300.

 

This is probably pretty simple but I cannot figure it out. Thanks in advance for your help.

4 Replies

@ponder10 

 

You start this out by referring to VLOOKUP. Although that could work, it's probably not the most efficient. From your description it sounds as if you would benefit from looking into the SUMIFS function instead. But some of the answer might also depend on how many different zip codes you might be expecting to use--at least I'd want to know what's the maximum.

 

And depending on what your answer is to that, I'd start to wonder if there isn't something in common (some other attribute) that identifies the zip codes that you're wanting to summarize this way and whether or not that attribute could be added to the table, to make it easier to use SUMIFS or even SUMIF.

 

Anyway, there are lots of resources, in addition to Excel's own Help text, that you can refer to for those functions. Since you already know VLOOKUP, I'm assuming you won't mind doing your own research into what fits best. By all means, come back here with any follow-up questions.

@mathetes 

 

The largest group will have 75 zip codes. I am trying multiple criteria in SUMIFS but for some reason the response is 0 rather than 300. Definitely user error.

@ponder10 

 

The second paragraph in my last message contained an implicit question: "....depending on what your answer is to that, I'd start to wonder if there isn't something in common (some other attribute) that identifies the zip codes that you're wanting to summarize this way and whether or not that attribute could be added to the table, to make it easier to use SUMIFS or even SUMIF," and now that you're acknowledging that there can be as many as 75 zip codes in any one grouping, I now really wonder what lies behind the selection of zip codes in the first place.

 

That aside, I think you're looking for some of the newer Dynamic Array functions. I'm just learning them myself. Here's a resource you might find helpful...https://exceljet.net/search?query=dynamic+arrays

 

 

@ponder10 

If SUMIFS() returns zero instead of expected value, wrong criteria is only one of the reasons. Another one if we have texts which represent numbers instead of numbers.