VLOOKUP formula

MrStressed

Board Regular
Joined
Mar 25, 2002
Messages
65
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.
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
OK, I was bit confused but here is what I do.

Vlook(cell your looking up, range, column, true or false)

Cell your looking up
Range: highlight the range you'll be looking up and in the top left white box next to the formula bar. Write a name for the range, something small is good. Like "data2002"
Column: the column number from where your range begins that has the info you want
true or false: write one if these words, if you want an exact match false, if you want it round up true.

I don't know if this answers your question, but maybe it can help simplify your formula and help you catch your error.
 
Upvote 0
Hiya,

I'm not sure how much this makes a difference but instead of a vlookup that looks like:

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

Basically swap the last $B11 argument to a false-applied to each vlookup in the sum.

Hope that helps,
Adam
 
Upvote 0
Adam

I tried your solution but the formula returned '#N/A', even for cells that should have had a result.

Can you think of anything else ?

Cheers
Simon
 
Upvote 0
On 2002-04-29 08:56, MrStressed wrote:
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.

I assume that you have a number of lookup values in column B from B11 on in your front sheet. How many are they? And can you give some examples of these string values?

And, what is the value of $H$2?
 
Upvote 0
Hi Aladin

I have several sheets that i'm trying to populate with this formula. The biggest sheet has about 270 'identifiers' that i am looking to return values to from the 'xxxSALCOM' sheets.

examples of these identifiers are
50166
50038
50040
50072

Cell 'H$2' contains the column reference for identifying the column to take data from on the 'xxxSALCOM' sheets, hopefully telling excel in which column to put the summed data.

Hope this makes sense.

Cheers
Simon
 
Upvote 0
I have several sheets that i'm trying to populate with this formula. The biggest sheet has about 270 'identifiers' that i am looking to return values to from the 'xxxSALCOM' sheets.

Simon, the foregoing is a confusing statement. My understanding is that you lookup a value (e.g., 50166) from within your front sheet in each of the SALCOM sheets and add up the returned values. Is this right?

examples of these identifiers are
50166
50038
50040
50072


Fine. Would you please check the following:

In your front sheet in an unused cell type:

=ISNUMBER(B11)

What do you get as result?

Cell 'H$2' contains the column reference for identifying the column to take data from on the 'xxxSALCOM' sheets,

Right. What is the value that H2 houses?

hopefully telling excel in which column to put the summed data.

This is again a confusing statement. My understanding is that H2 must house a number which indicates where to look in the range B$3:$V$900 to find a value associated with a lookup value.

FYI, the VLOOKUP function has the following syntax:

VLOOKUP(lookup-value,lookup-table,where-to-look-in-the-llokup-table,desired-match-type)

where desired-match-type can be either approximate (indicated by 1 or TRUE) or exact (indicated by 0 or FALSE).

Aladin
 
Upvote 0
Aladin

YOU SAID - My understanding is that you lookup a value (e.g., 50166) from within your front sheet in each of the SALCOM sheets and add up the returned values. Is this right? THAT IS CORRECT.

=ISNUMBER(B11) my result - FALSE

H2 value is 7 (B2=1, C2=2 etc) and is my 'where to look in the lookup table'

sorry to be so confusing.

Cheers
Simon
 
Upvote 0
On 2002-04-30 03:40, MrStressed wrote:
Aladin

YOU SAID - My understanding is that you lookup a value (e.g., 50166) from within your front sheet in each of the SALCOM sheets and add up the returned values. Is this right? THAT IS CORRECT.

=ISNUMBER(B11) my result - FALSE

H2 value is 7 (B2=1, C2=2 etc) and is my 'where to look in the lookup table'

sorry to be so confusing.

Cheers
Simon

One more thing:

Go to the midsSALCOM sheet and type in an empty cell:

=ISNUMER(B3)

What result do you get?

Aladin
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top