Vlookup and hlookup

[junkforhr]

Hello,

I have one column of data.

What I'like to return is below. I'm not sure how to combine vlookup and hlookup or if even these are the formulas to use.

Any help is appreciated.

Desired result

Lookup1	Virtual-abc
Lookup2	Virtual-678
Lookup3	Virtual-lpo

Sample data below where row1 etc are, will contain text and or numeric values. The value I wish to return will always start with the word Virtual.

Sample data in Column A

Lookup1 Row1 Row2 Row3 Row4 Row5 Row6 Row7 Row8 Virtual-abc Lookup2 Row1 Row2 Row3 Row4 Row5 Row6 Row7 Virtual-678 Row7 Lookup3 Row1 Row2 Virtual-lpo Row4 Row5 Row6 Row7 Row8 Row9 Row10 Row11

 

[Peter_SSs]

See if something like this would suit you.
I have assumed that the left column of the results may not always start with "Lookup" but whatever follows a blank row. If it does always start with "Lookup" then you could also use this in C2, copied down

=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(A$2:A$40)/(LEFT(A$2:A$40,6)="Lookup"),ROWS(C$2:C2))),"")

22 09 21.xlsm
	A	B	C	D
1
2	Lookup1		Lookup1	Virtual-abc
3	Row1		Lookup2	Virtual-678
4	Row2		Lookup3	Virtual-lpo
5	Row3
6	Row4
7	Row5
8	Row6
9	Row7
10	Row8
11	Virtual-abc
12
13	Lookup2
14	Row1
15	Row2
16	Row3
17	Row4
18	Row5
19	Row6
20	Row7
21	Virtual-678
22	Row7
23
24
25	Lookup3
26	Row1
27	Row2
28	Virtual-lpo
29	Row4
30	Row5
31	Row6
32	Row7
33	Row8
34	Row9
35	Row10
36	Row11
37
Extract

Cell Formulas
Range	Formula
C2:C9	C2	=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(A$2:A$40)/((A$2:A$40<>"")*(A$1:A$39="")),ROWS(C$2:C2))),"")
D2:D9	D2	=IF(C2="","",INDEX(A:A,AGGREGATE(15,6,ROW(A$2:A$40)/(LEFT(A$2:A$40,7)="Virtual"),ROWS(D$2:D2))))

 

[junkforhr]

Worked perfectly, thanks for your help with this.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.