Sleeper: Help needed with VLOOKUP

[rama4672]

I Have the following vlookup table,

=VLOOKUP(A2,Sheet1!$A$1:$C$11,3)

what I want it to do is if there is more than 1 match in the criteria then i want it to total the matching columns, and also if it does not find a match the insert 0 instead if #N/A

TIA

Ian

[jacksonworld]

The adding of matches in vlookup is definitely possible, although I don't how to do it.

Have a look at this thread. http://www.vbaexpress.com/forum/showthread.php?t=4495
It contains a function that displays the matches in one cell, comma separated. I am sure that function can be tweaked for your purposes.

As far as replacing the #N/A with 0, that can be done easily enough.

Replace your formula with:

=IF(ISNA(VLOOKUP(A2,Sheet1!$A$1:$C$11,3)),0,(VLOOKUP(A2,Sheet1!$A$1:$C$11,3)))

[rama4672]

Thank you for the reply Jacksonworld
That sorts the #N/A problem out, as for the match it needs to add the total just like the formula to sumproduct where it only sums what matches the criteria

Regards

Ian

[geekgirlau]

Is there any reason you're not using SumProduct for this?

[rama4672]

As there are 8 sheets in the spreadsheet i figured it would be easier to us a lookup, the sheets are sunday through to saturday and the last one is the totals where it takes all the values from the match and totals them up



Regards



Ian

[geekgirlau]

SumIf should work for this one