V-Lookup with 2 options

**Huddle** · 12-21-2010, 12:11 PM

I am wanting to do a V-Lookup but have the option on which varible is used.

My columns include the building number, address, system, alarm number...

If the building number is selected through a drop down I want it to autopopulate the address, Commander and Inspector

If the alarm number is selected through a drop down I want it to autopopulate the building number, address, system, commander and inspector

The data would be pulled from three seperate sheets

See the attached example

Is this something that is doable or can you only autopopulate using only one varible (bldg number)

**Huddle** · 12-21-2010, 12:12 PM

Here's the example

**NBVC** · 12-21-2010, 12:21 PM

You can use VLOOKUPs within an IF function....

e.g. IF(X1<>"",VLOOKUP(X1,.....),IF(Y1<>"",VLOOKUP(Y1,.....),""))

this will take X1 as priority.. if it has an entry, then it will use that value and lookup the items as defined by the first Vlookup, otherwise it will look if Y1 has a value, then lookup that based on the 2nd Vlookup parameters... if both X1 and Y1 are blank, then the cell will stay blank.

Modify to suit.

**Huddle** · 12-21-2010, 04:27 PM

That works. I used the below formula.
=IF($C172<>"",VLOOKUP($C172,LookupTbl_Facilities!$A:$H,2,),IF($K172<>"",VLOOKUP($K172,tbl_AlarmAccts!$A:$I,3,)," "))

One problem I have is if there is an alarm number but not a building number known entering the alarm number fills in the address but I'd like it to autofill the building number from the alarm list.

If I put a VLookup formula in the building number cell (C172) for this it messes up the other formulas.

Somehow I think this can be done in the IF statements but I just can't grasp it.

**NBVC** · 12-21-2010, 04:31 PM

If you mean that the building number column can either have a hard coded entry or a formula result, then that cannot be done with native Excel. It is one or the other, or use 2 separate columns.

If you have to have it in one column, you would be looking into using event macros (VBA).