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]

Are the results meant to increase as they do in B6:B9, or should they be the same as in B16:B17?

Perhaps this as long as the empty cells are actually empty and not blank?

Book2
	A	B	C
1	IDs	Results	Value
2			H27-12
3
4
5
6	ID	H27-12
7	ID	H27-12
8	ID	H27-12
9	ID	H27-12
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
Sheet2

Cell Formulas
Range	Formula
B2:B22	B2	=IF(A2="ID",LOOKUP("zzz",C$1:C1),"")

 

[rex759]

Hello,
Thank you for your reply. My mistake on range B6:B9. You are correct in your results. What does the Lookup("ZZZ") do? I have never used it.

 

[jasonb75]

It's an approximate lookup, so it finds the last entry in the list that has a value less than or equal to "zzz".
Note that it only works with text (alphanumeric strings are still classed as text for formula purposes), if you were looking for numbers then you would need to use a number like 1e+100 instead (that's 1 followed by 100 zero's in scientific notiation).

Formula blanks will cause problems with this method as "" is evaluated as text so the formula will see that and not the string above it, a workaround for that would require a more complex and less efficient formula.

 

[rex759]

Ok, thank you very much.

 

[rex759]

I found that the format changes from time to time in the report. Column A might have an ID on the same line as the Value. I solved that by adding another condition and using the Search function.

I have a new issue which sounds like I have to use the 1e+100 that you were talking about. Occasionally, there is text in column C. Out of the 6,000 rows, there were only 4 occurrences. If there is a way to get around that, great. If not, it might not be the end of the world.

Book1
	A	B	C
1			H27-12
2
3
4
5	ID	H27-12
6	ID	H27-12
7	ID	oil	oil
8	ID	oil
9
10			Car
11
12
13
14	ID	H27-18	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:B21	B2	=IF(AND(A2="ID",ISNUMBER(SEARCH("h27",C2))),C2,IF(A2="ID",LOOKUP("zzz",C$1:C2),""))

 

[jasonb75]

Try this one in B2 and fill down

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

 

[rex759]

hmmm, I got #N/A errors

 

[jasonb75]

That might be cos of the missing headers, the formula assumed data starting in row 2 as in the first example. Without headers it needs to be adjusted back with the first formula in B1 instead of B2

=IF(A1="ID",LOOKUP(2,1/(LEFT(C1:C$1,4)="H27-"),C1:C$1),"")

 

[petertenthije]

Would you be opposed to an additional column?

In D1 you place =C1
In D2 you place =if(len($C2)>0,$C2,$D1)
In B1 you place =if(len($A1)>0,$D1,””)

Drag the formulas in B1 and D2 down to the end of the data range.