Dropdown Menu & VLookup formula not working

Dear All

I make this table, and I try to make search feature by using drop down menu and vlookup formula, I use data validation to add HVAC Unit & Number, the problems am facing the following:

1. After I add HRU units number, I change HVAC Unit to FCU and I try to add drop down list for number from below table after I do this step through data validation it will change also number for HVAC unit.

2. After changing number it will not return correct data for Building, Location and Level from below table.

Please advise what is the mistake I did and how can I correct it. " I want to learn "

Thank u it worked! but again had the issue with after adding data validation list of AHU as drop down list for number, I change to HRU and from number I go to data validation and I choose that HRU number.

If I go back to AHU the number that I choose previously it gone and only number for HRU it will be showing how comes?

I open the test file same issue exist, my problem is how to add data validation for HRU and FCU without changing data validation list of AHU ?

If I choose FCU , numbers will show for example 302 and 304

If I choose HRU, numbers will show for example 5

What is the correct process to do it , please teach me that

Thank u it worked but can u explain to me how does the two formula worked ?

Also there is no more easy formula similar to the one I inserted?

After click on Evaluate Formula it shows only value as 71 for example