Vlookup backwards

[mac_see]

Book2
	A	B	C	D	E	F
1	1	A	B	C	D	E
2
3	7	0	0	0
4	0	0	0	0
5	2	0	0	0
6	1	0	0	0
7	6	0	0	0
8	6	0	0	0
9	6	25	275	2225		Y
10	8	0	-2225	2225
11	7	0	-2225	2225
12	6	25	-1950	1950
13	0	30	-1620	1620
14	5	60	-960	960
15	9	70	-190	190
16	4	80	690	-690
17	3	90	1680	-1680	N
18	0	45	2175	2325		Y
19	9	0	-2325	2325
20	7	25	-2050	2050
Sheet1

I think its OFFSET but not sure how to use it.

I want to lookup the letter "Y" in column F and the moment I get second instance of "Y", I want the corresponding value in column C. In this example its 2325

Not sure how to vlookup the second value and how to return the value which is on the left side as vlookup doesn't return value backwards.

Maxi

 

[Peter_SSs]

mac_see

If your data area is very large, this may not be the best method, but let's get started with something. In this case I have used a 'helper' column G which counts the Y values so we know where the second one is.

I have assumed that your data only goes to row 20, so you will have to adjust ranges if that is not the case.

Formula in G3 (copied down) is:
=IF(F3="","",COUNTIF(F$3:F3,"Y"))
Column G could be hidden if desired.

Formula in H2 is:
=LOOKUP(2,G3:G20,D3:D20)

Mr Excel.xls
	A	B	C	D	E	F	G	H
1	1	A	B	C	D	E	Count Y	Result
2								2325
3	7	0	0	0
4	0	0	0	0
5	2	0	0	0
6	1	0	0	0
7	6	0	0	0
8	6	0	0	0
9	6	25	275	2225		Y	1
10	8	0	-2225	2225
11	7	0	-2225	2225
12	6	25	-1950	1950
13	0	30	-1620	1620
14	5	60	-960	960
15	9	70	-190	190
16	4	80	690	-690
17	3	90	1680	-1680	N
18	0	45	2175	2325		Y	2
19	9	0	-2325	2325
20	7	25	-2050	2050
Vlookup Backwards

 

[fairwinds]

Hi,

Try:

=INDEX(D2:D30,MATCH("Y",INDEX(F2:F30,MATCH("Y",F2:F30,0)+1):F30,0)+MATCH("Y",F2:F30,0))

 

[Aladin Akyurek]

Book1
	A	B	C	D	E	F	G	H
1	1	A	B	C	D	E
2								Y
3	7	0	0	0				2
4	0	0	0	0				2325
5	2	0	0	0
6	1	0	0	0
7	6	0	0	0
8	6	0	0	0
9	6	25	275	2225		Y
10	8	0	-2225	2225
11	7	0	-2225	2225
12	6	25	-1950	1950
13	0	30	-1620	1620
14	5	60	-960	960
15	9	70	-190	190
16	4	80	690	-690
17	3	90	1680	-1680	N
18	0	45	2175	2325		Y
19	9	0	-2325	2325
20	7	25	-2050	2050
Sheet1

H4:

=INDEX($D$2:$D$20,SMALL(IF($F$2:$F$20=H2,ROW($F$2:$F$20)-ROW($F$2)+1),H3))

which must be confirmed with control+shift+enter, not just with enter.

 

[mac_see]

What if I also want to find the row number of the second instance of Y? In this example, it should be 16

 

[fairwinds]

=MATCH("Y",INDEX(F1:F30,MATCH("Y",F1:F30,0)+1):F30,0)+MATCH("Y",F1:F30,0)

 

[mac_see]

A little difficult this time:

I could not paste my entire sheet due to space restriction but let me tell you that I have data till the 52nd column, top row is for heading, second row is blank and next 50 rows for my data.

From top I want to see the difference between the count of letter "Y" and letter "N" in my example, first instance of letter "Y" comes at row # 9th and till that time there was no instance of letter "N" hence the difference is 1 (one minus zero) then I go down I get first instance of letter "N" at row # 17th and at this point of time the difference goes down to zero (one minus one) as we have one instance each of both the letters "Y" and "N". In the next row, we get second instance of "Y" hence the difference goes back to one (Two minus one).

Go on checking this till the end of the data 52nd row. if the difference is greater than -1 (-1, 0 or any positive number) then it is accepted and I want to get the last value of column "D" i.e., D52 but at any row if the difference goes down to -2, then I want to get the value of that row corresponding to column D

In my example, the result should be 2050 as the difference at any point never came down to -2

Not sure if I have explained this properly. I can send you my file if you want.

 

[fairwinds]

Try:

=IF(ISNA(MATCH(-2,COUNTIF(OFFSET(F3,,,ROW(INDIRECT("1:50"))),"Y")-COUNTIF(OFFSET(E3,,,ROW(INDIRECT("1:50"))),"N"),0)),D52,INDEX(D3:D52,MATCH(-2,COUNTIF(OFFSET(F3,,,ROW(INDIRECT("1:50"))),"Y")-COUNTIF(OFFSET(E3,,,ROW(INDIRECT("1:50"))),"N"),0)))

This formula must be confirmed with Ctrl + Shift + Enter, not just with Enter.