# Vlookup backwards

#### mac_see

##### Active Member
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

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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

Hi,

Try:

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

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.

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

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

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.

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.

Replies
1
Views
576
Replies
6
Views
104
Replies
4
Views
206
Replies
3
Views
209
Replies
6
Views
1K

1,214,256
Messages
6,118,520
Members
448,830
Latest member
Drew Terp

### 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.

### Which adblocker are you using?

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

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