Reverse Search for Value

[rex759]

I thought this was going to be simple but I cannot figure out what direction to take. In column B, I am looking for a formula that will do a search backwards up column C if Column A as the word ID in it.
Column B has the results I am looking for. The number of rows and IDs appear randomly, they are never the same amount between each other. The values always start with H27-.

Any help is appreciated

Book1
	A	B	C
1	IDs	Results	Value
2			H27-12
3
4
5
6	ID	H27-12
7	ID	H27-13
8	ID	H27-14
9	ID	H27-15
10
11
12
13
14
15			H27-18
16	ID	H27-18
17	ID	H27-18
18
19
20
21			H27-1324
22	ID	H27-1324
Sheet6

 

[jasonb75]

That makes it a lot simpler, Peter. It will need a different formula in D2 though, =IF(LEFT($C2,4)="H27-",$C2,$D1)

 

[rex759]

So not opposed to an additional column but the other formula almost worked. B14 should be H27-18

Book1
	A	B	C
1			H27-12
2
3
4
5	ID	H27-12
6	ID	H27-12
7	ID	H27-12	oil
8	ID	H27-12
9
10			Car
11
12
13
14	ID	H27-12	H27-18
15	ID	H27-18
16	ID	H27-18
17			train
18
19
20			H27-1324
21	ID	H27-1324
Sheet6 (2)

Cell Formulas
Range	Formula
B2	B2	=IF(A2="ID",LOOKUP(2,1/(LEFT(C1:C$1,4)="H27-"),C1:C$1),"")
B3:B21	B3	=IF(A3="ID",LOOKUP(2,1/(LEFT(C$1:C2,4)="H27-"),C$1:C2),"")

 

[jasonb75]

I can see the problem

Without headers it needs to be adjusted back with the first formula in B1 instead of B2

Yours is still in B2

 

[rex759]

=IF(AND(A2="ID",ISNUMBER(SEARCH("h27",C2))),C2,IF(A2="ID",LOOKUP(2,1/(LEFT(C$1:C1,4)="H27-"),C$1:C1),""))

This seems to work, what do you think?

 

[rex759]

Ok, Once I put it in B1, that did the trick without adding another column. I will apply that to my workbook and see if there are any other roadblocks. I appreciate all your help.