# Vlookup problem in checking university category



## Kevincwk2000 (Dec 14, 2022)

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:-

NoName (English)by formularesult compareby Category lookup71Peking UniversityC9correctC924Chongqing UniversityDFCAcorrectDFCA45Hunan UniversityDFCBcorrectDFCB36Hainan University211correct211Bohai UniversityC9incorrectnot found


----------



## mohadin (Dec 15, 2022)

Hi
Try

```
=If( Vlookup(...)....=0
```
Instead of is blank


----------



## Peter_SSs (Dec 15, 2022)

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 (Dec 15, 2022)

Peter_SSs said:


> .. 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 (Dec 18, 2022)

Peter_SSs said:


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


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


----------



## Peter_SSs (Dec 18, 2022)

Kevincwk2000 said:


> thanks Peter,
> The formula looks work,


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


----------

