Vlookup problem in checking university category

[Kevincwk2000]

I work in school and need to check the university information from the provided sheet yearly.
for convenient, I try to create a formula for checking but not fully successful.

Is there any guy to provide an solution?

the formula is
=IF(ISBLANK(VLOOKUP(C7,'mainland U'!$B$6:$E$118,2,0)),IF(ISBLANK(VLOOKUP(C7,'mainland U'!$B$6:$E$118,3,0)),IF(ISBLANK(VLOOKUP(C7,'mainland U'!$B$6:$E$118,4,0)),"211","DFCB"),"DFCA"),"C9")

The result are:-

No	Name (English)	by formula	result compare	by Category lookup
71	Peking University	C9	correct	C9
24	Chongqing University	DFCA	correct	DFCA
45	Hunan University	DFCB	correct	DFCB
36	Hainan University	211	correct	211
	Bohai University	C9	incorrect	not found

 

[mohadin]

Hi
Try

 =If( Vlookup(...)....=0

Instead of is blank

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Peter_SSs]

.. the best solution often varies by version.

If you have the LET & FILTER functions, try ..

=LET(a,{1,1,1},f,FILTER('mainland U'!$C$6:$E$118,'mainland U'!$B$6:$B$118=C7,a),IF(OR(f=a),"not found",FILTER({"C9","DFCA","DFCB"},f<>"","211")))

 

[Kevincwk2000]

If you have the LET & FILTER functions, try ..

=LET(a,{1,1,1},f,FILTER('mainland U'!$C$6:$E$118,'mainland U'!$B$6:$B$118=C7,a),IF(OR(f=a),"not found",FILTER({"C9","DFCA","DFCB"},f<>"","211")))

thanks Peter,
The formula looks work, but the function LET, FILTER are new to me, I have to investigate how they work.

 

[Peter_SSs]

thanks Peter,
The formula looks work,

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