VLOOKUP
 

Hello,

I have created a cable lookup sheet where a technician can choose an
out-plant cable, enter the fiber optic count number, and then be returned
multiple fields of data on the cable such as fiber color, tube color, binder
color, etc..

My trouble is this I created a drop down list that references a worksheet
with the cable names. In one field I want the tech to choose the cable
location from the drop down list (H6). Next they enter the count of the fiber
(I6). I need the functions in F6, G6, J6, and K6 to be able to reference both
the cable ID and the fiber count within the same function.

Here is an example of the function as I have it now in F6 which returns
results solely on input in I6...
=IF(ISERROR(VLOOKUP(I6,Table!$B$2:$F$14989,2,FALSE )),"",VLOOKUP(I6,Table!$B$2:$F$14989,2,FALSE))

I want the same lookup to find its results based on both the cable ID and
the fiber count, this is because all cable locations will have the same fiber
count€¦

I can provide the file for review.

Thank you very much,
Todd

VLOOKUP
 

You may send the fle to me.

Essentially you have to create a column in front of the LOOKUP sheet which
combines the cable id and Fibre count using
=B1&C1

and then use the combination in VLLOKUP like
=VLOOKUP(H6&I6,....)

"FP Novice" wrote:

Hello,

I have created a cable lookup sheet where a technician can choose an
out-plant cable, enter the fiber optic count number, and then be returned
multiple fields of data on the cable such as fiber color, tube color, binder
color, etc..

My trouble is this I created a drop down list that references a worksheet
with the cable names. In one field I want the tech to choose the cable
location from the drop down list (H6). Next they enter the count of the fiber
(I6). I need the functions in F6, G6, J6, and K6 to be able to reference both
the cable ID and the fiber count within the same function.

Here is an example of the function as I have it now in F6 which returns
results solely on input in I6...
=IF(ISERROR(VLOOKUP(I6,Table!$B$2:$F$14989,2,FALSE )),"",VLOOKUP(I6,Table!$B$2:$F$14989,2,FALSE))

I want the same lookup to find its results based on both the cable ID and
the fiber count, this is because all cable locations will have the same fiber
count€¦

I can provide the file for review.

Thank you very much,
Todd

VLOOKUP
 

Hi,

Your if the results are text you might use

=INDEX(Table!$C$2:$C$14989,SUMPRODUCT(--(I6=Table!$B$2:$B$14989),--(H6=Table!$D$2:$D$14989),ROW(2:14989)),0)

In this example I am assuming the cable values are in column D of your table.


=IF(ISERROR(VLOOKUP(I6,Table!$B$2:$F$14989,2,FALSE )),"",VLOOKUP(I6,Table!$B$2:$F$14989,2,FALSE))
--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"FP Novice" wrote:

Hello,

I have created a cable lookup sheet where a technician can choose an
out-plant cable, enter the fiber optic count number, and then be returned
multiple fields of data on the cable such as fiber color, tube color, binder
color, etc..

My trouble is this I created a drop down list that references a worksheet
with the cable names. In one field I want the tech to choose the cable
location from the drop down list (H6). Next they enter the count of the fiber
(I6). I need the functions in F6, G6, J6, and K6 to be able to reference both
the cable ID and the fiber count within the same function.

Here is an example of the function as I have it now in F6 which returns
results solely on input in I6...
=IF(ISERROR(VLOOKUP(I6,Table!$B$2:$F$14989,2,FALSE )),"",VLOOKUP(I6,Table!$B$2:$F$14989,2,FALSE))

I want the same lookup to find its results based on both the cable ID and
the fiber count, this is because all cable locations will have the same fiber
count€¦

I can provide the file for review.

Thank you very much,
Todd

VLOOKUP
 

The file is on its way, thanks Sheeloo.

"Sheeloo" wrote:

You may send the fle to me.

Essentially you have to create a column in front of the LOOKUP sheet which
combines the cable id and Fibre count using
=B1&C1

and then use the combination in VLLOKUP like
=VLOOKUP(H6&I6,....)

"FP Novice" wrote:

Hello,

I have created a cable lookup sheet where a technician can choose an
out-plant cable, enter the fiber optic count number, and then be returned
multiple fields of data on the cable such as fiber color, tube color, binder
color, etc..

My trouble is this I created a drop down list that references a worksheet
with the cable names. In one field I want the tech to choose the cable
location from the drop down list (H6). Next they enter the count of the fiber
(I6). I need the functions in F6, G6, J6, and K6 to be able to reference both
the cable ID and the fiber count within the same function.

Here is an example of the function as I have it now in F6 which returns
results solely on input in I6...
=IF(ISERROR(VLOOKUP(I6,Table!$B$2:$F$14989,2,FALSE )),"",VLOOKUP(I6,Table!$B$2:$F$14989,2,FALSE))

I want the same lookup to find its results based on both the cable ID and
the fiber count, this is because all cable locations will have the same fiber
count€¦

I can provide the file for review.

Thank you very much,
Todd

VLOOKUP
 

=IF(ISERROR(VLOOKUP(H6&"-"&I6,Table!A:F,3,FALSE)),"",VLOOKUP(H6&"-"&I6,Table!A:F,3,FALSE))

That worked, thanks Sheeloo

Todd

"Sheeloo" wrote:

You may send the fle to me.

Essentially you have to create a column in front of the LOOKUP sheet which
combines the cable id and Fibre count using
=B1&C1

and then use the combination in VLLOKUP like
=VLOOKUP(H6&I6,....)

"FP Novice" wrote:

Hello,

I have created a cable lookup sheet where a technician can choose an
out-plant cable, enter the fiber optic count number, and then be returned
multiple fields of data on the cable such as fiber color, tube color, binder
color, etc..

My trouble is this I created a drop down list that references a worksheet
with the cable names. In one field I want the tech to choose the cable
location from the drop down list (H6). Next they enter the count of the fiber
(I6). I need the functions in F6, G6, J6, and K6 to be able to reference both
the cable ID and the fiber count within the same function.

Here is an example of the function as I have it now in F6 which returns
results solely on input in I6...
=IF(ISERROR(VLOOKUP(I6,Table!$B$2:$F$14989,2,FALSE )),"",VLOOKUP(I6,Table!$B$2:$F$14989,2,FALSE))

I want the same lookup to find its results based on both the cable ID and
the fiber count, this is because all cable locations will have the same fiber
count€¦

I can provide the file for review.

Thank you very much,
Todd