| Home |
| Search |
| Today's Posts |
|
#7
|
|||
|
|||
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. |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Using a Vlookup to return values in a data list? | Excel Worksheet Functions | |||
| Vlookup based on two lookup values | Excel Worksheet Functions | |||
| VLOOKUP: type or paste values | Excel Worksheet Functions | |||
| vlookup to see 2 values? | Excel Worksheet Functions | |||
| VLOOKUP FOR MULTIPLE VALUES | Setting up and Configuration of Excel | |||
Threaded Mode
