Vlookup 1 column to the left

[Tom]

I would like to create a formula in cell c1 that will lookup C2:C100
and find the first occurance of "N" and use the number to the left
(column B) in a further calculation. In this case 4 would be the
result. I am adding data each day to the top of the spreadsheet so
the rows will move down 1 cell that's why I need the formula

Thanks

Tom


A B C

1 3
7 5 Y
8 6 Y
7 3 Y
9 8 Y
3 4 N
6 5 Y
7 9 N
3 4 N

 

[T. Valko]

VLOOKUP only works to the right.

Try this:

=INDEX(B:B,MATCH("N",C:C,0))

 

[Tom]

VLOOKUP only works to the right.

Try this:

=INDEX(B:B,MATCH("N",C:C,0))

--
Biff
Microsoft Excel MVP







- Show quoted text -

That worked great. How can I find the last occurence of "N" in a
grouping if there were for eg. three underneath each other? Cell C1
would return 9 cell b8 in eg below.

Thanks

Tom

A B C
1 3
7 5 Y
8 6 Y
7 3 Y
9 8 Y
3 4 N
6 5 N
7 9 N
6 2 Y

 

[T. Valko]

That worked great. How can I find the last occurence of "N" in a
grouping if there were for eg. three underneath each other? Cell C1
would return 9 cell b8 in eg below.

Thanks

Tom

A B C
1 3
7 5 Y
8 6 Y
7 3 Y
9 8 Y
3 4 N
6 5 N
7 9 N
6 2 Y
Try this:

=LOOKUP(2,1/(C2:C9="n"),B2:B9)

You can't use entire columns as range references with this formula unless
you're using Excel 2007.

 

[Tom]

That worked great.  How can I find the last occurence of "N" in a
grouping if there were for eg. three underneath each other?  Cell C1
would return 9 cell b8 in eg below.

Thanks

Tom

A  B   C
1   3
7   5   Y
8   6   Y
7   3   Y
9   8   Y
3   4   N
6   5   N
7   9   N
6   2   Y



Try this:

=LOOKUP(2,1/(C2:C9="n"),B2:B9)

You can't use entire columns as range references with this formula unless
you're using Excel 2007.

That worked well however I have one last question. How would I create
a formula in column D that would pick the number in column B that
corresponded with the last occurence of N either by itself or in a
group . Results shown in D to be clearer on what I am trying to do.

Thanks

A B C D
5 3 Y 9
7 5 Y 9
8 6 Y 9
7 9 N 9
9 8 Y 4
3 7 N 4
6 5 N 4
3 4 N 4
6 2 Y 1
7 5 Y 1
8 6 Y 1
7 3 Y 1
9 8 Y 1
3 6 N 1
6 9 N 1
7 1 N 1
6 2 Y 6
7 5 Y 6
3 6 N 6
7 3 Y 9
9 8 Y 9
3 4 N 9
6 5 N 9
7 9 N 9
6 2 Y

 

[T. Valko]

That worked well however I have one last question. How would I create
a formula in column D that would pick the number in column B that
corresponded with the last occurence of N either by itself or in a
group . Results shown in D to be clearer on what I am trying to do.

Thanks

A B C D
5 3 Y 9
7 5 Y 9
8 6 Y 9
7 9 N 9
9 8 Y 4
3 7 N 4
6 5 N 4
3 4 N 4
6 2 Y 1
7 5 Y 1
8 6 Y 1
7 3 Y 1
9 8 Y 1
3 6 N 1
6 9 N 1
7 1 N 1
6 2 Y 6
7 5 Y 6
3 6 N 6
7 3 Y 9
9 8 Y 9
3 4 N 9
6 5 N 9
7 9 N 9
6 2 Y
This seems to work but uses a helper column.

Enter this formula in D2 and copy down as needed:

=IF(AND(C2="n",OR(C3="y",C3="")),B2,"")

Enter this formula in E2 and copy down as needed (adjust for the end of
range):

=IF(COUNTIF(C2:C$100,"n"),INDEX(D2$100,MATCH(TRUE,INDEX(D2$100<>"",,1),0)),"")

You can hide the helper column if desired.

 

[Tom]

That worked well however I have one last question. How would I create
a formula in column D that would pick the number in column B that
corresponded with the last occurence of N either by itself or in a
group . Results shown in D to be clearer on what I am trying to do.

Thanks

A B C D
5 3 Y 9
7 5 Y 9
8 6 Y 9
7 9 N 9
9 8 Y 4
3 7 N 4
6 5 N 4
3 4 N 4
6 2 Y 1
7 5 Y 1
8 6 Y 1
7 3 Y 1
9 8 Y 1
3 6 N 1
6 9 N 1
7 1 N 1
6 2 Y 6
7 5 Y 6
3 6 N 6
7 3 Y 9
9 8 Y 9
3 4 N 9
6 5 N 9
7 9 N 9
6 2 Y



This seems to work but uses a helper column.

Enter this formula in D2 and copy down as needed:

=IF(AND(C2="n",OR(C3="y",C3="")),B2,"")

Enter this formula in E2 and copy down as needed (adjust for the end of
range):

=IF(COUNTIF(C2:C$100,"n"),INDEX(D2$100,MATCH(TRUE,INDEX(D2$100<>"",,1),0)),"")

You can hide the helper column if desired.

This is great. Is there a way to create the result that I have shown
in column c with a formula? The formula in c is basically if a(n)>=b(n
+1) then Y, if a(n)<=b(n+1) then N, if N the the formula compares if
a(n-1) is >= a(n) with result in c(n-1) if c(n-1) is still N then it
compares a(n-2) >= b(n-1) with result in c(n-2) and so on until the
result in c(n-x) is Y. If Y then the formula starts again eg if a(n-
x)>=b(n-x+1).



A B C D
5 3 Y 9
7 5 Y 9
8 6 Y 9
7 9 N 9
9 8 Y 4
3 7 N 4
6 5 N 4
3 4 N 4
6 2 Y 1
7 5 Y 6
9 6 Y 9
4 3 N 9
2 8 N 9
3 6 N 9
6 9 Y 1
7 1 Y 2
9 2 Y 5
7 5 Y 7
3 6 N 7
6 3 N 7
9 7 Y 8
3 4 N 8
6 5 N 8
7 9 N 8
6 8

 

[T. Valko]

This is great. Is there a way to create the result that I have shown

in column c with a formula? The formula in c is basically if a(n)>=b(n
+1) then Y, if a(n)<=b(n+1) then N, if N the the formula compares if
a(n-1) is >= a(n) with result in c(n-1) if c(n-1) is still N then it
compares a(n-2) >= b(n-1) with result in c(n-2) and so on until the
result in c(n-x) is Y. If Y then the formula starts again eg if a(n-
x)>=b(n-x+1).

Sorry, not following you at all on this!