Jay
Bork! Bork! Bork!
+2,006|5328|London, England
I have an excel database where I need to find the 2nd and 3rd instance of a specified value (vlookup suffices for the 1st instance), where the value is "n".

So basically I need it to find "n" in column 1, and then spit out the value located in the third column.

I can't change the value of "n" to "n1" etc because it's acting as an on/off and determined by an IF statement elsewhere in the database.

It will look something like this in the end which is why I need it to find the 2nd and 3rd instance and why I can't just an OFFSET:

y
n
n
y
y
n

So in this example it would have to know to find it in rows 2, 3 and 6. If I just use OFFSET it will just spit out 2, 3, 4.

Any takers?

Edit - oh, and it has to be a function because it will be a dynamic database with the values "y" and "n" changing as data is inputted. So no macros.

Last edited by Jay (2013-03-15 21:05:58)

"Ah, you miserable creatures! You who think that you are so great! You who judge humanity to be so small! You who wish to reform everything! Why don't you reform yourselves? That task would be sufficient enough."
-Frederick Bastiat
Jay
Bork! Bork! Bork!
+2,006|5328|London, England
Nevermind, figured out a workaround using COUNTIF.

I created a second column next to the first that looks like:
y y
n n1
n n2
y y
y y
n n3

using: =IF(A3="n","n"&COUNTIF($A$2:A3,"n"),"y")

I can then just VLOOKUP n1, n2 and n3 individually.

Last edited by Jay (2013-03-15 22:00:59)

"Ah, you miserable creatures! You who think that you are so great! You who judge humanity to be so small! You who wish to reform everything! Why don't you reform yourselves? That task would be sufficient enough."
-Frederick Bastiat
Uzique The Lesser
Banned
+382|4224
keep us posted

Board footer

Privacy Policy - © 2024 Jeff Minard