VLOOKUP formula

MrStressed · Apr 29th, 2002, 09:56 AM

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,gl SALCOM!$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)+VLO OKUP($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.

Parra · Apr 29th, 2002, 10:04 AM

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.

Asala42 · Apr 29th, 2002, 10:27 AM

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

MrStressed · Apr 30th, 2002, 02:59 AM

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

Aladin Akyurek · Apr 30th, 2002, 03:17 AM

Quote:

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,gl SALCOM!$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)+VLO OKUP($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?

MrStressed · Apr 30th, 2002, 03:35 AM

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

Aladin Akyurek · Apr 30th, 2002, 04:15 AM

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

MrStressed · Apr 30th, 2002, 04:40 AM

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

Aladin Akyurek · Apr 30th, 2002, 04:44 AM

Quote:

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

MrStressed · Apr 30th, 2002, 04:47 AM

Aladin

=ISNUMBER(B3) - my result - TRUE

Cheers
Simon

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 29th, 2002, 09:56 AM	#1
MrStressed Board Regular Join Date: Mar 2002 Location: Kent, the Garden of England, that no one has bothered to weed. Posts: 62	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,gl SALCOM!$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)+VLO OKUP($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.

Apr 29th, 2002, 10:04 AM	#2
Parra Board Regular Join Date: Feb 2002 Location: Los Angeles, CA Posts: 752	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.

Apr 29th, 2002, 10:27 AM	#3
Asala42 Board Regular Join Date: Feb 2002 Location: Southfield,MI USA Posts: 1,030	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

Apr 30th, 2002, 02:59 AM	#4
MrStressed Board Regular Join Date: Mar 2002 Location: Kent, the Garden of England, that no one has bothered to weed. Posts: 62	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

Apr 30th, 2002, 03:35 AM	#6
MrStressed Board Regular Join Date: Mar 2002 Location: Kent, the Garden of England, that no one has bothered to weed. Posts: 62	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

Apr 30th, 2002, 04:15 AM	#7
Aladin Akyurek MrExcel MVP Join Date: Feb 2002 Location: The Hague Posts: 50,319	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

Apr 30th, 2002, 04:40 AM	#8
MrStressed Board Regular Join Date: Mar 2002 Location: Kent, the Garden of England, that no one has bothered to weed. Posts: 62	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

Apr 30th, 2002, 04:47 AM	#10
MrStressed Board Regular Join Date: Mar 2002 Location: Kent, the Garden of England, that no one has bothered to weed. Posts: 62	Aladin =ISNUMBER(B3) - my result - TRUE Cheers Simon