PDA

View Full Version : Sleeper: Help needed with VLOOKUP



rama4672
08-07-2005, 07:09 PM
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
08-07-2005, 07:29 PM
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
08-07-2005, 07:38 PM
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
08-07-2005, 08:39 PM
Is there any reason you're not using SumProduct for this?

rama4672
08-07-2005, 08:52 PM
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
08-07-2005, 09:00 PM
SumIf should work for this one