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.

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.

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.

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.

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.

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
...
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.

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.

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
...
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.