Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old May 18th 05, 10:46 AM
sansk_23
 
Posts: n/a
Default 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   Report Post  
Old May 18th 05, 11:32 AM
 
Posts: n/a
Default

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   Report Post  
Old May 18th 05, 12:01 PM
sansk_23
 
Posts: n/a
Default

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   Report Post  
Old May 18th 05, 12:07 PM
 
Posts: n/a
Default

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   Report Post  
Old May 18th 05, 12:42 PM
sansk_23
 
Posts: n/a
Default

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   Report Post  
Old May 18th 05, 12:52 PM
 
Posts: n/a
Default

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   Report Post  
Old May 18th 05, 01:03 PM
Duke Carey
 
Posts: n/a
Default

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   Report Post  
Old May 18th 05, 01:31 PM
sansk_23
 
Posts: n/a
Default

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.











Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Using a Vlookup to return values in a data list? rtjeter Excel Worksheet Functions 2 April 26th 05 05:56 AM
Vlookup based on two lookup values Trip Excel Worksheet Functions 2 April 8th 05 06:25 PM
VLOOKUP: type or paste values Rochelle Excel Worksheet Functions 5 April 8th 05 03:23 PM
vlookup to see 2 values? alex Excel Worksheet Functions 12 March 21st 05 08:47 PM
VLOOKUP FOR MULTIPLE VALUES RITA Setting up and Configuration of Excel 1 February 10th 05 11:42 PM


All times are GMT +1. The time now is 05:19 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017