About Us

Teethless mama

2 is the second column in your data range
0 is the exact match

"vcff" wrote:

tnks a lot for helping me solved my problem. Just for knowledge, wat is the
reason for 2,0?

"Teethless mama" wrote:

In D1 =VLOOKUP(G1,J1:K100,2,0)
Adjust your range to suit

"vcff" wrote:

Hi "Teethless mama"
I have a problem similar to Q2 but what I want is to have a input cell and
the color to be shown in the output cell(formula)

col D col G col J col K
output input 1 red
2 white
3 black
4 silver
...
99 gold

when 4 was typed in G1, silver will be shown in D1

Pls help as I am new to formulas. Tnks & R

"Teethless mama" wrote:

Answere for question 1:
Assuming your data in B2:G2

in B3 =INDEX(B2:G2,MATCH(TRUE,B2:G20,0))
ctrl + shift + enter (not just enter)

Answer for question 2:
Assuming your data in A2:B100 and the criteria in C2

=SUMPRODUCT(--(A1:A100=C2),--(A1:A100<=C2+12),B1:B100)
Note: you can not use a whole column example A:A or B:B

"RLind" wrote:

1. I have 2 rows as follows:
Month
Cash Flow

What I want is to write a formula in a cell that looks across all values in
the cash flow row and finds the first cell where cash flow is positive, then
looks up and pulls the corresponding month.
For example:
Month: 1 2 3 4 5 6
Cash: -100 -300 -15 18 12 22

The formula in the cell should find the 18 in the cash flow row and return 4
in the month row. I now would know the first month we reached positive cash
flow. I cannot use a MAX or MIN function because values are not consistant
(see month 4 vs 5) and the values may change.
----------------------------------
Question 2: I want to write a formula that looks up a value in a table and
then pulls the sum of certain rows in another table. For example:

I have a monthly table where each month has a dollar value as follows:
Col A Col B
Month $
1 1000
2 1200
3 800
4 975
...
60 2300
61 2450
62 2000
...
72 2800

I have a "driver" or input cell where I would type in the number "60". I
want the cell with the formula in it to go to the monthly table (see above)
and find the row that has "60" in Column A, then go to Column B and sum that
number plus the next 12 months. So if typed "60" into my driver cell, then I
would want my formula cell to sum months 60 - 72. If I changed my driver
cell to "12" then i would want it to go to the monthly table and find 12 in
column A, then sum column B months 12-24.
For context, this would be useful to answer the question "3 years from now
(36 months) after we have completed our improvements, what will we expect the
following year's revenue to be?"

THANKS!

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Reusing formula	Tony29	Excel Discussion (Misc queries)	7	September 7th 06 03:34 AM
Lookup Formula???	mkuethe	Excel Worksheet Functions	3	May 18th 06 05:52 PM
Formula Problem - interrupted by #VALUE! in other cells!?	Ted	Excel Worksheet Functions	17	November 25th 05 05:18 PM
Formula checking multiple worksheets	sonic-the-mouse	Excel Worksheet Functions	2	June 5th 05 07:48 PM
Formula checking multiple worksheets	sonic-the-mouse	Excel Worksheet Functions	2	June 5th 05 03:28 AM

Menu

About Us