SUMPRODUCT and LEFT(LEN())

[Sir Babydum GBE]

Hi

This is a formula one more than VBA...

In the attached, I need to try and get Babydum's results to include the various ways Babydum appears in the list. In reality, if there were only two possibilities it would be easy, but on a real list there are many names and some have three or four suffixes - so I tried using Left(len but I couldn't get it to work.

Any ideas?

Cheers

BD

[Bob Phillips]

=SUMPRODUCT(--(ISNUMBER(SEARCH($G5,$B$3:$B$29))),--(H$4=$C$3:$C$29)*($D$3:$D$29))

[Sir Babydum GBE]

=SUMPRODUCT(--(ISNUMBER(SEARCH($G5,$B$3:$B$29))),--(H$4=$C$3:$C$29)*($D$3:$D$29))

Excellent! Incidentally - what's ISNUMBER doing there?

[Bob Phillips]

Oh, I just chucked it in for a bit of sport.

Because it doesn't work without it.

And now for a reasonable answer.

The Search is looking through the range for the value in G5. Search will find that string anywhere in the lookup value, and return the character index if it finds it. If it doesn't find it, it returns #N/A. So the ISNUMBER is used to check each return value to be a number or an error, and return a TRUE/FALSE, which as you know SP gobbles up for breakfast.

[Sir Babydum GBE]

Well it's thoroughly geniousy.

BD