Vlookups Based on Validation Data
 

Hi Guys/Girls

I have done this once before so I know it can be done.
What im looking for is basically this.
I have a Listing for (Sites) that has Site_ID's site name ETC...

Now there are Sites that have the Exact same Site_ID because they fall under
the same Vlan.

Now I have a lookup form that I use for quick lookups of IP's etc.
What is done is in Cell C2 you enter a Site ID now in the Second cell (C3) I
want to have a Data validation / Drop down List that will show a listing of
all the Site names that have that Site ID. (C2)
then for cells C4 on it will do a lookup based on Cells C2 and C3
For the Cells C4 im sure Ill use a Index and Match combination its just the
listing based on cell C2 that I can not remember how to do for the Life of Me.


Heres a example of the Data in Listing
Site ID Site Name IP
A1: 200 B1: Server bank C1:127.0.0.1
A2: 350 B2: Data Centre C2: 127.0.0.2
A3: 350 B3: networking C3: 127.0.0.3
A4: 389 B4: xyyz C4: 127.0.0.4

Vlookups Based on Validation Data
 

OK Found what I need :)

http://www.contextures.com/xlDataVal13.html

Basically Im using
=OFFSET(*Start of column Data*,MATCH(C2,*Column Data*,0)-1,1,COUNTIF(*Column
Data*,C2),1)
in Data Validation List option :)

"Digi" wrote:

Hi Guys/Girls

I have done this once before so I know it can be done.
What im looking for is basically this.
I have a Listing for (Sites) that has Site_ID's site name ETC...

Now there are Sites that have the Exact same Site_ID because they fall under
the same Vlan.

Now I have a lookup form that I use for quick lookups of IP's etc.
What is done is in Cell C2 you enter a Site ID now in the Second cell (C3) I
want to have a Data validation / Drop down List that will show a listing of
all the Site names that have that Site ID. (C2)
then for cells C4 on it will do a lookup based on Cells C2 and C3
For the Cells C4 im sure Ill use a Index and Match combination its just the
listing based on cell C2 that I can not remember how to do for the Life of Me.


Heres a example of the Data in Listing
Site ID Site Name IP
A1: 200 B1: Server bank C1:127.0.0.1
A2: 350 B2: Data Centre C2: 127.0.0.2
A3: 350 B3: networking C3: 127.0.0.3
A4: 389 B4: xyyz C4: 127.0.0.4

Vlookups Based on Validation Data
 

OK Issue number 2 now

in cells C4-C10
im using the following to try use values from Cells C2 and C3 to do the
lookups for the respective cells.
so im using this.
=INDEX(Server_Name,MATCH(1,(C2=Site_ID)*(C3=Site_N ame),0)) and using
CTRL+SHIFT+ENTER to allow it to work, But im getting a #NUM error

Pweeze help.

"Digi" wrote:

OK Found what I need :)

http://www.contextures.com/xlDataVal13.html

Basically Im using
=OFFSET(*Start of column Data*,MATCH(C2,*Column Data*,0)-1,1,COUNTIF(*Column
Data*,C2),1)
in Data Validation List option :)

"Digi" wrote:

Hi Guys/Girls

I have done this once before so I know it can be done.
What im looking for is basically this.
I have a Listing for (Sites) that has Site_ID's site name ETC...

Now there are Sites that have the Exact same Site_ID because they fall under
the same Vlan.

Now I have a lookup form that I use for quick lookups of IP's etc.
What is done is in Cell C2 you enter a Site ID now in the Second cell (C3) I
want to have a Data validation / Drop down List that will show a listing of
all the Site names that have that Site ID. (C2)
then for cells C4 on it will do a lookup based on Cells C2 and C3
For the Cells C4 im sure Ill use a Index and Match combination its just the
listing based on cell C2 that I can not remember how to do for the Life of Me.


Heres a example of the Data in Listing
Site ID Site Name IP
A1: 200 B1: Server bank C1:127.0.0.1
A2: 350 B2: Data Centre C2: 127.0.0.2
A3: 350 B3: networking C3: 127.0.0.3
A4: 389 B4: xyyz C4: 127.0.0.4