VLOOKUP formula

[MrStressed]

All, sorry in advance for the length of this post.

I have a sheet that i am trying to populate by using a VLOOKUP formula, searching for a numeric string in 12 sheets and populating the result of the search in my front sheet. After writing the formula I have got results where i should'nt have i.e. the numeric string has no related values (apart from 0) to populate as a total to the front sheet.
Additionally I'm confused as to the term 'Range_lookup' which appears as the fourth section of the VLOOKUP formula wizard. what should I be using here ? my original range of numeric strings from my front sheet or ranges from the sheets of data I am looking at.

Heres my formula at present..
=VLOOKUP($B11,midsSALCOM!B$3:$V$905,H$2,$B11)+VLOOKUP($B11,birmSALCOM!B$3:$V$912,H$2,$B11)+VLOOKUP($B11,avctSALCOM!B$3:$V$900,H$2,$B11)+VLOOKUP($B11,lseSALCOM!$B$3:$V$886,H$2,$B11)+VLOOKUP($B11,edSALCOM!$B$3:$V$936,H$2,$B11)+VLOOKUP($B11,glSALCOM!$B$3:$V$918,H$2,$B11)+VLOOKUP($B11,udSALCOM!$B$3:$V$934,H$2,$B11)+VLOOKUP($B11,neSALCOM!$B$3:$V$913,H$2,$B11)+VLOOKUP($B11,nwSALCOM!$B$3:$V$914,H$2,$B11)+VLOOKUP($B11,wlSALCOM!$B$3:$V$908,H$2,$B11)+VLOOKUP($B11,ykSALCOM!$B$3:$V$904,H$2,$B11)+VLOOKUP($B11,clSALCOM!$B$3:$V$906,H$2,$B11)

Any help you can offer would be great.

 

[MrStressed]

Aladin

Sorry about this -

both the results for the =ISNUMBER(XX) formulas was TRUE.

I typed the formula into the wrong sheet - DOH!

Sorry
Simon

 

[Aladin Akyurek]

On 2002-04-30 03:47, MrStressed wrote:
Aladin

=ISNUMBER(B3) - my result - TRUE

Cheers
Simon

What we have here a data type mismatch that also leads to #N/A's when unexpected.

Go to the front sheet.
Copy an empty, unused cell.
Select all of the lookup values in B from B11 on.
Activate the option Edit|Paste Special >Add.

Now test again:

=ISNUMBER(B11)

which should return TRUE.

When you're thru with this, I'll propose a layout for your front sheet to compute the desired sums/totals for each of your lookup values.

 

[MrStressed]

Aladin

My error on the =ISNUMBER(XX) formula. Both results were TRUE.

I'm ready - hit me!

Cheers
Simon

 

[Aladin Akyurek]

In your front sheet:

In C11 enter:

=SUMIF(D11:O11,"<>#N/A")

Double click on the fill handle (the little black square) in the lower right corner of C11.

In D11 enter and double click on its fill handle:

=VLOOKUP($B11,midsSALCOM!B$3:$V$905,H$2,0)

In E11 enter and double click on its fill handle:

VLOOKUP($B11,birmSALCOM!B$3:$V$912,H$2,0)

In F11 enter and double click on its fill handle:

=VLOOKUP($B11,avctSALCOM!B$3:$V$900,H$2,0)

Follow up the above logic until you have all of your 12 lookup tables.

The above is more efficient than trying to avoid #N/A's in a single mega-formula.

Aladin