Remember Me?

#1
May 18th 05, 10:46 AM
 sansk_23 Posts: n/a
vlookup only a set of values.

How do i get : e,g. only -ve or only +ve values when i do a vlookup on a
database table / array of data ?
Can this be somehow incoporated in the a single formula with VLOOKUP ? -
instead of retrieving the complete data first and then giving another
condition for getting only the -ve(s) or +ve(s).

Pls. help.

br, Sk.

#2
May 18th 05, 11:32 AM
 Posts: n/a

Hi
Are you wanting to bring back a value and then declare it -ve or +ve, or do
you only want to bring back a -ve or +ve?

--
Andy.

"sansk_23" wrote in message
...
How do i get : e,g. only -ve or only +ve values when i do a vlookup on a
database table / array of data ?
Can this be somehow incoporated in the a single formula with VLOOKUP ? -
instead of retrieving the complete data first and then giving another
condition for getting only the -ve(s) or +ve(s).

Pls. help.

br, Sk.

#3
May 18th 05, 12:01 PM
 sansk_23 Posts: n/a

Hi !!

The output of the process / formula should be either a -ve or a +ve number
depending on the requirement.
Pls. suggest a way out.

br, SK.

"Andy B" wrote:

Hi
Are you wanting to bring back a value and then declare it -ve or +ve, or do
you only want to bring back a -ve or +ve?

--
Andy.

"sansk_23" wrote in message
...
How do i get : e,g. only -ve or only +ve values when i do a vlookup on a
database table / array of data ?
Can this be somehow incoporated in the a single formula with VLOOKUP ? -
instead of retrieving the complete data first and then giving another
condition for getting only the -ve(s) or +ve(s).

Pls. help.

br, Sk.

#4
May 18th 05, 12:07 PM
 Posts: n/a

I would suggest you post some sample data in plain text and a couple of
examples so we can see what you expect to get in certain conditions.

--
Andy.

"sansk_23" wrote in message
...
Hi !!

The output of the process / formula should be either a -ve or a +ve number
depending on the requirement.
Pls. suggest a way out.

br, SK.

"Andy B" wrote:

Hi
Are you wanting to bring back a value and then declare it -ve or +ve, or
do
you only want to bring back a -ve or +ve?

--
Andy.

"sansk_23" wrote in message
...
How do i get : e,g. only -ve or only +ve values when i do a vlookup on
a
database table / array of data ?
Can this be somehow incoporated in the a single formula with VLOOKUP
? -
instead of retrieving the complete data first and then giving another
condition for getting only the -ve(s) or +ve(s).

Pls. help.

br, Sk.

#5
May 18th 05, 12:42 PM
 sansk_23 Posts: n/a

Ok !!
If i have some data (product sales shortfall - Target Vs. Actuals) like this
e,g. for State - California :-

Jan / Feb / Mar / Apr / May / June
Prod A -20 10 -30 40 -10 50
Prod B 20 -10 30 40 10 -40
Prod C -20 -10 -30 -40 -10 -50
Prod D 20 10 30 40 10 50
and so on .....
Now i have similar data for different Territories / States in different
sheets.
In a different sheet, if i want the monthly performance of a product in
different states in separate Product wise Sheets, i would apply the hlookup
formula.
e,g. Now i would have different sheets as SheetProdA, SheetProdB ......and
so on.
The data i want in SheetProdA should be like : -
Jan / Feb / Mar / Apr / May / June
California -20 0 -30 0 -10 0
Toronto
New York
Las Vegas

likewise ...... so on , the data for differnet Products in different sheets.
How do i get the desired output as shown above from a single hlookup formula ?

Hope this clarifies the output required (only -ve values in this case).
Pls. suggest.

"Andy B" wrote:

I would suggest you post some sample data in plain text and a couple of
examples so we can see what you expect to get in certain conditions.

--
Andy.

"sansk_23" wrote in message
...
Hi !!

The output of the process / formula should be either a -ve or a +ve number
depending on the requirement.
Pls. suggest a way out.

br, SK.

"Andy B" wrote:

Hi
Are you wanting to bring back a value and then declare it -ve or +ve, or
do
you only want to bring back a -ve or +ve?

--
Andy.

"sansk_23" wrote in message
...
How do i get : e,g. only -ve or only +ve values when i do a vlookup on
a
database table / array of data ?
Can this be somehow incoporated in the a single formula with VLOOKUP
? -
instead of retrieving the complete data first and then giving another
condition for getting only the -ve(s) or +ve(s).

Pls. help.

br, Sk.

#6
May 18th 05, 12:52 PM
 Posts: n/a

Hi
You have a few options. If you already have a table set up (which you seem
to suggest) for the results using VLOOKUP, rather than starting from
scratch, you could format the cells to only show values below 0. This can be
done either with conditional formatting (from Format menu) or by wrapping
the VLOOKUP in an IF function:
=IF(yourVLOOKUP<0,yourVLOOKUP,0).
Another way would be to use SUMPRODUCT, rather than VLOOKUP, but would mean
a rewrite for you.

--
Andy.

"sansk_23" wrote in message
news
Ok !!
If i have some data (product sales shortfall - Target Vs. Actuals) like
this
e,g. for State - California :-

Jan / Feb / Mar / Apr / May / June
Prod A -20 10 -30 40 -10 50
Prod B 20 -10 30 40 10 -40
Prod C -20 -10 -30 -40 -10 -50
Prod D 20 10 30 40 10 50
and so on .....
Now i have similar data for different Territories / States in different
sheets.
In a different sheet, if i want the monthly performance of a product in
different states in separate Product wise Sheets, i would apply the
hlookup
formula.
e,g. Now i would have different sheets as SheetProdA, SheetProdB ......and
so on.
The data i want in SheetProdA should be like : -
Jan / Feb / Mar / Apr / May / June
California -20 0 -30 0 -10
0
Toronto
New York
Las Vegas

likewise ...... so on , the data for differnet Products in different
sheets.
How do i get the desired output as shown above from a single hlookup
formula ?

Hope this clarifies the output required (only -ve values in this case).
Pls. suggest.

"Andy B" wrote:

I would suggest you post some sample data in plain text and a couple of
examples so we can see what you expect to get in certain conditions.

--
Andy.

"sansk_23" wrote in message
...
Hi !!

The output of the process / formula should be either a -ve or a +ve
number
depending on the requirement.
Pls. suggest a way out.

br, SK.

"Andy B" wrote:

Hi
Are you wanting to bring back a value and then declare it -ve or +ve,
or
do
you only want to bring back a -ve or +ve?

--
Andy.

"sansk_23" wrote in message
...
How do i get : e,g. only -ve or only +ve values when i do a vlookup
on
a
database table / array of data ?
Can this be somehow incoporated in the a single formula with VLOOKUP
? -
instead of retrieving the complete data first and then giving
another
condition for getting only the -ve(s) or +ve(s).

Pls. help.

br, Sk.

#7
May 18th 05, 01:03 PM
 Duke Carey Posts: n/a

If I understand your question correctly, you don't want the postiive values,
right?

=MIN(yourlookup formula, 0)

"sansk_23" wrote:

Ok !!
If i have some data (product sales shortfall - Target Vs. Actuals) like this
e,g. for State - California :-

Jan / Feb / Mar / Apr / May / June
Prod A -20 10 -30 40 -10 50
Prod B 20 -10 30 40 10 -40
Prod C -20 -10 -30 -40 -10 -50
Prod D 20 10 30 40 10 50
and so on .....
Now i have similar data for different Territories / States in different
sheets.
In a different sheet, if i want the monthly performance of a product in
different states in separate Product wise Sheets, i would apply the hlookup
formula.
e,g. Now i would have different sheets as SheetProdA, SheetProdB ......and
so on.
The data i want in SheetProdA should be like : -
Jan / Feb / Mar / Apr / May / June
California -20 0 -30 0 -10 0
Toronto
New York
Las Vegas

likewise ...... so on , the data for differnet Products in different sheets.
How do i get the desired output as shown above from a single hlookup formula ?

Hope this clarifies the output required (only -ve values in this case).
Pls. suggest.

"Andy B" wrote:

I would suggest you post some sample data in plain text and a couple of
examples so we can see what you expect to get in certain conditions.

--
Andy.

"sansk_23" wrote in message
...
Hi !!

The output of the process / formula should be either a -ve or a +ve number
depending on the requirement.
Pls. suggest a way out.

br, SK.

"Andy B" wrote:

Hi
Are you wanting to bring back a value and then declare it -ve or +ve, or
do
you only want to bring back a -ve or +ve?

--
Andy.

"sansk_23" wrote in message
...
How do i get : e,g. only -ve or only +ve values when i do a vlookup on
a
database table / array of data ?
Can this be somehow incoporated in the a single formula with VLOOKUP
? -
instead of retrieving the complete data first and then giving another
condition for getting only the -ve(s) or +ve(s).

Pls. help.

br, Sk.

#8
May 18th 05, 01:31 PM
 sansk_23 Posts: n/a

thanks.

"Andy B" wrote:

Hi
You have a few options. If you already have a table set up (which you seem
to suggest) for the results using VLOOKUP, rather than starting from
scratch, you could format the cells to only show values below 0. This can be
done either with conditional formatting (from Format menu) or by wrapping
the VLOOKUP in an IF function:
=IF(yourVLOOKUP<0,yourVLOOKUP,0).
Another way would be to use SUMPRODUCT, rather than VLOOKUP, but would mean
a rewrite for you.

--
Andy.

"sansk_23" wrote in message
news
Ok !!
If i have some data (product sales shortfall - Target Vs. Actuals) like
this
e,g. for State - California :-

Jan / Feb / Mar / Apr / May / June
Prod A -20 10 -30 40 -10 50
Prod B 20 -10 30 40 10 -40
Prod C -20 -10 -30 -40 -10 -50
Prod D 20 10 30 40 10 50
and so on .....
Now i have similar data for different Territories / States in different
sheets.
In a different sheet, if i want the monthly performance of a product in
different states in separate Product wise Sheets, i would apply the
hlookup
formula.
e,g. Now i would have different sheets as SheetProdA, SheetProdB ......and
so on.
The data i want in SheetProdA should be like : -
Jan / Feb / Mar / Apr / May / June
California -20 0 -30 0 -10
0
Toronto
New York
Las Vegas

likewise ...... so on , the data for differnet Products in different
sheets.
How do i get the desired output as shown above from a single hlookup
formula ?

Hope this clarifies the output required (only -ve values in this case).
Pls. suggest.

"Andy B" wrote:

I would suggest you post some sample data in plain text and a couple of
examples so we can see what you expect to get in certain conditions.

--
Andy.

"sansk_23" wrote in message
...
Hi !!

The output of the process / formula should be either a -ve or a +ve
number
depending on the requirement.
Pls. suggest a way out.

br, SK.

"Andy B" wrote:

Hi
Are you wanting to bring back a value and then declare it -ve or +ve,
or
do
you only want to bring back a -ve or +ve?

--
Andy.

"sansk_23" wrote in message
...
How do i get : e,g. only -ve or only +ve values when i do a vlookup
on
a
database table / array of data ?
Can this be somehow incoporated in the a single formula with VLOOKUP
? -
instead of retrieving the complete data first and then giving
another
condition for getting only the -ve(s) or +ve(s).

Pls. help.

br, Sk.

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post rtjeter Excel Worksheet Functions 2 April 26th 05 05:56 AM Trip Excel Worksheet Functions 2 April 8th 05 06:25 PM Rochelle Excel Worksheet Functions 5 April 8th 05 03:23 PM alex Excel Worksheet Functions 12 March 21st 05 09:47 PM RITA Setting up and Configuration of Excel 1 February 11th 05 12:42 AM

All times are GMT +1. The time now is 04:56 PM.