# VLOOKUP - Return sum of all matches in Range?

1. ## VLOOKUP - Return sum of all matches in Range?

Hi,

Is it possible (I'm sure it is - I simply don't know) to specify a vlookup to find ALL matches and return the sum of them?

The issue we have is this current forumla (pasted below) looks for a corresponding cell, and then reads the adjacent value. However it stops after the first match....the first match has 0 in it. The issue is there are a further 2 instances of that 'matching' value....which has adjacent cells with positive values in.

Current Formula: =IF(ISERROR(VLOOKUP(O14,'sheet1'!\$B\$4:\$AG\$1116,32,FALSE)),"0",VLOOKUP(O14,'sheet1'!\$B\$4:\$AG\$1116,32,FALSE))-K14+L14

I understand what is happening - looking up an exact value (hence FALSE) ...if it's not found, it displays a "0"....if it is, it runs pulls the data from the first match.

2. =sumif(sheet1!\$b\$4:\$b\$1116,O14,'sheet1'!\$ag\$4:\$AG\$1116)-K14+L14

Regards

Dav

3. Or sumproduct would do

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

VBA Noob

4. Thanks guys - did the trick.

For some reason I was stuck on the VLOOKUP function - didn't think to look elsewhere

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1