Reverse VLOOKUP, HLOOKUP

[Michael515]

Looking at this image: View image: ex 4

I want to be able to in cell C2 have a function that looks at Cell B2 and if it says "Yes" look at Cell B1 and it it reads "No" return value A1.
For cell C3, what should happen is that it looks at B3 sees "Yes", then looks at cell B2 sees another "Yes" so it goes up until it sees "No" which is B1 and therefore cell C3 returns A1, thus cell C2 and C3 have the same returning value.

Thanks for any and all help!

 

[LloydFinancials]

See if this helps:

Excel 2010
	A	B	C
1	1	No
2	2	Yes	1
3	3	Yes	1
4	4	Yes	1
5	5	No	5
6	6	No	6
7	7	Yes	6
8	8	Yes	6
9	9	Yes	6
10	10	No	10
11	11	No	11
12	12	No	12
13	13	Yes	12
Sheet1

Cell Formulas
Range	Formula
C2	{=INDEX($A$1:$A$13,SMALL(IF($B$1:$B2<>"No","",ROW($B$1:$B2)),COUNTIF($B$1:$B2,"No")))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Luke

 

[LloydFinancials]

I was working off one of my earlier templates and see that this will suffice without the <> "NO" condition:

=INDEX($A$1:$A$13,SMALL(IF($B$1:$B2="No",ROW($B$1:$B2)),COUNTIF($B$1:$B2,"No")))

 

[Michael515]

Thanks for the reply! I'm curious, why did you use a countif function? I'm trying to return the value in cell A1 (which is text and not a number) to the cell adjacent to a Yes, and if there is a No, there should be nothing in the adjacent cell. So it should look something like:

View image: ex 5

I'm looking for the function that would make the outputs happen in column c.

 

[LloydFinancials]

Excel 2010
	A	B	C
1	a	No
2	b	Yes	a
3	c	Yes	a
4	d	No
5	e	No
6	f	Yes	e
7	g	Yes	e
8	h	Yes	e
9	i	No
10	j	Yes	i
11	k	No
12	l	Yes	k
Sheet1

Cell Formulas
Range	Formula
C2	{=IF(B2="No","",INDEX($A$1:$A$13,SMALL(IF($B$1:$B2<>"No","",ROW($B$1:$B2)),COUNTIF($B$1:$B2,"No"))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[LloydFinancials]

The count if is the k for the Small formula. The formula in C6 for instance counts 3 "No"s so it wants the third smallest "No" or the third "No", and then get that index in column A.

 

[bosco_yip]

Another option,

In C2, copy down :

=IF(B2="Yes",LOOKUP(2,1/(B$1:B2="No"),A$1:A2),"")

 

[Michael515]

Awesome guys that worked thanks!

Also, if you guys know anything about VBA or Macros, or know of someone who does, can you send them to this thread I posted a week ago? I didn't get any replies, but maybe someone can lend a quick hand!

http://www.mrexcel.com/forum/excel-questions/890396-pulling-data-rand-built-simulation-macros.html

 

[LloydFinancials]

reply to that thread with the word "Bump" to bring attention to it