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
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
54,356
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,210
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,057
Messages
5,835,161
Members
430,343
Latest member
t0m_c

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top