Vlookup backwards

mac_see

Active Member
Joined
Oct 15, 2002
Messages
419
Book2
ABCDEF
11ABCDE
2
37000
40000
52000
61000
76000
86000
96252752225Y
1080-22252225
1170-22252225
12625-19501950
13030-16201620
14560-960960
15970-190190
16480690-690
173901680-1680N
1804521752325Y
1990-23252325
20725-20502050
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
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,486
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGH
11ABCDECount YResult
22325
37000 
40000 
52000 
61000 
76000 
86000 
96252752225Y1
1080-22252225 
1170-22252225 
12625-19501950 
13030-16201620 
14560-960960 
15970-190190 
16480690-690 
173901680-1680N 
1804521752325Y2
1990-23252325 
20725-20502050 
Vlookup Backwards
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Book1
ABCDEFGH
11ABCDE
2Y
370002
400002325
52000
61000
76000
86000
96252752225Y
1080-22252225
1170-22252225
12625-19501950
13030-16201620
14560-960960
15970-190190
16480690-690
173901680-1680N
1804521752325Y
1990-23252325
20725-20502050
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

Active Member
Joined
Oct 15, 2002
Messages
419

ADVERTISEMENT

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

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
=MATCH("Y",INDEX(F1:F30,MATCH("Y",F1:F30,0)+1):F30,0)+MATCH("Y",F1:F30,0)
 

mac_see

Active Member
Joined
Oct 15, 2002
Messages
419
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

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,331
Messages
5,571,578
Members
412,407
Latest member
ElmerCC
Top