Help in Formula

[ashani]

hi experts,

i wonder if you could help me, I have a list of 15000 names in database and I want excel to review in Column A if there is either Test1 to Test 5 or Test20 to Test24 then the heading in Yellow i.e. ABC or DEF comes next to it. If there's something else then leave it blank.
I would really appreciate your help.

 

[Scott Huish]

It would be easier if you stacked your result table like this, then you could just do a VLOOKUP.

Test1	ABC
Test2	ABC
Test3	ABC
Test4	ABC
Test5	ABC
Test20	DEF
Test21	DEF
Test22	DEF
Test23	DEF
Test24	DEF

 

[ashani]

what formula would you use for that specifically?

 

[Scott Huish]

Book2
	A	B	C	D	E	F	G	H
1	Test1	ABC					Test1	ABC
2	Test24	DEF					Test2	ABC
3							Test3	ABC
4							Test4	ABC
5							Test5	ABC
6							Test20	DEF
7							Test21	DEF
8							Test22	DEF
9							Test23	DEF
10							Test24	DEF
Sheet1

Cell Formulas
Range	Formula
B1:B2	B1	=VLOOKUP(A1,$G$1:$H$10,2,0)

 

[ashani]

Thank you - how would you leave it blank if not found

 

[Scott Huish]

=IFERROR(VLOOKUP(A1,$G$1:$H$10,2,0),"")

 

[ashani]

amazing - thank you so much for your prompt response