|
Vlookup with multiple corresponding values
Looking for some help, where I work the old system for booking out items from
our store involved a pen and paper, ive now computerised this. I now have the staff booking out items, as I go I have been changing this to make it more user friendly and this has included using Vlookup so the staff only have to find the item number and the Vlookup completes the item description. =VLOOKUP(E2,$B$3:C749,2,FALSE) Ive now come to a brick wall, basically 99% of our stocked items are given unique stock numbers however we carry Consumable items (ZONNS), we have approx 30 different items all under ZCONNS. I have been surfing and reading articles but I cannot find the right formula that I need, what I would like to do is for a staff member to type in the item number ZCONNS and for it to then bring up a drop down list or similar for us to choose the correct item to book out of store. Below is a sample list of some of the consumables: ZCONNS Bootlace Ferrule Violet 0.25mm ZCONNS Bootlace Ferrule Pink 0.34mm ZCONNS Bootlace Ferrule White 0.5mm ZCONNS RJ11's - 4 contacts - STR ZCONNS RJ11's - 4 contacts - Solid ZCONNS RJ12's - 6 contacts - STR ZCONNS RJ12's - 6 contacts - Solid ZCONNS RJ45's - Stranded - STR ZCONNS RJ45's - Solid ZCONNS RJ45's - Shielded Stranded - STR ZCONNS RJ45's - Shielded Solid ZCONNS Resistors etc - All Sorts ZCONNS Cable Ties pkt100x(100x2.5) Natural ZCONNS Cable Ties pkt100x(100x2.5) Black Any help is greatly appreciated. Regards David |
Vlookup with multiple corresponding values
Vlookup is the wrong function for you, it returns only the first value that
it finds matching the one you lookup however many columns you specify, as you know. To make a drop down list you can do a data validation: Date, validation, allow: list. Press the cell selection button that has just appeared below the list option then choose the data that you want to be allowed (each cell chosen will represent a separate item on the list). Then you can set whether your staff are allowed to input other values or not and what it says when they put in the wrong value etc. If you want to do a vlookup function for this, you will need to use a unique value for each product, as that is how vlookup works. At least, that's what I know! "Dudeone" wrote: Looking for some help, where I work the old system for booking out items from our store involved a pen and paper, ive now computerised this. I now have the staff booking out items, as I go I have been changing this to make it more user friendly and this has included using Vlookup so the staff only have to find the item number and the Vlookup completes the item description. =VLOOKUP(E2,$B$3:C749,2,FALSE) Ive now come to a brick wall, basically 99% of our stocked items are given unique stock numbers however we carry Consumable items (ZONNS), we have approx 30 different items all under ZCONNS. I have been surfing and reading articles but I cannot find the right formula that I need, what I would like to do is for a staff member to type in the item number ZCONNS and for it to then bring up a drop down list or similar for us to choose the correct item to book out of store. Below is a sample list of some of the consumables: ZCONNS Bootlace Ferrule Violet 0.25mm ZCONNS Bootlace Ferrule Pink 0.34mm ZCONNS Bootlace Ferrule White 0.5mm ZCONNS RJ11's - 4 contacts - STR ZCONNS RJ11's - 4 contacts - Solid ZCONNS RJ12's - 6 contacts - STR ZCONNS RJ12's - 6 contacts - Solid ZCONNS RJ45's - Stranded - STR ZCONNS RJ45's - Solid ZCONNS RJ45's - Shielded Stranded - STR ZCONNS RJ45's - Shielded Solid ZCONNS Resistors etc - All Sorts ZCONNS Cable Ties pkt100x(100x2.5) Natural ZCONNS Cable Ties pkt100x(100x2.5) Black Any help is greatly appreciated. Regards David |
Vlookup with multiple corresponding values
Thanks for the reply, yes your right regarding the Vlookup, what I think I
need to do is use an IF statement but although ive used excel a lot its not been writing formulas. So I still need the Vlookup as I want to be able to find the item description and for it to populate a set row with said description. But in the formula I need it to be able to recognise ZCONNS and give me a list of all associated items and then allow the user to choose the correct item. Here is a further sample of our item numbers: ZVIDCAB001 1694A Black ZVIDCAB002 1694A Blue ZVIDCAB003 1694A Red ZVIDCAB004 1694A Breen ZCONNS Bootlace Ferrule Violet 0.25mm ZCONNS Bootlace Ferrule Pink 0.34mm ZCONNS Bootlace Ferrule White 0.5mm ZCONNS RJ11's - 4 contacts - STR ZCONNS RJ11's - 4 contacts - Solid What I think I need is for the statement to read something like this: If E3 = ZCONNS then display list, Else ,VLOOKUP(E2,$B$3:C749,2,FALSE) However putting this in Excel is not as easy. "exoticdisease" wrote: Vlookup is the wrong function for you, it returns only the first value that it finds matching the one you lookup however many columns you specify, as you know. To make a drop down list you can do a data validation: Date, validation, allow: list. Press the cell selection button that has just appeared below the list option then choose the data that you want to be allowed (each cell chosen will represent a separate item on the list). Then you can set whether your staff are allowed to input other values or not and what it says when they put in the wrong value etc. If you want to do a vlookup function for this, you will need to use a unique value for each product, as that is how vlookup works. At least, that's what I know! "Dudeone" wrote: Looking for some help, where I work the old system for booking out items from our store involved a pen and paper, ive now computerised this. I now have the staff booking out items, as I go I have been changing this to make it more user friendly and this has included using Vlookup so the staff only have to find the item number and the Vlookup completes the item description. =VLOOKUP(E2,$B$3:C749,2,FALSE) Ive now come to a brick wall, basically 99% of our stocked items are given unique stock numbers however we carry Consumable items (ZONNS), we have approx 30 different items all under ZCONNS. I have been surfing and reading articles but I cannot find the right formula that I need, what I would like to do is for a staff member to type in the item number ZCONNS and for it to then bring up a drop down list or similar for us to choose the correct item to book out of store. Below is a sample list of some of the consumables: ZCONNS Bootlace Ferrule Violet 0.25mm ZCONNS Bootlace Ferrule Pink 0.34mm ZCONNS Bootlace Ferrule White 0.5mm ZCONNS RJ11's - 4 contacts - STR ZCONNS RJ11's - 4 contacts - Solid ZCONNS RJ12's - 6 contacts - STR ZCONNS RJ12's - 6 contacts - Solid ZCONNS RJ45's - Stranded - STR ZCONNS RJ45's - Solid ZCONNS RJ45's - Shielded Stranded - STR ZCONNS RJ45's - Shielded Solid ZCONNS Resistors etc - All Sorts ZCONNS Cable Ties pkt100x(100x2.5) Natural ZCONNS Cable Ties pkt100x(100x2.5) Black Any help is greatly appreciated. Regards David |
Vlookup with multiple corresponding values
Have a look at Debra Dalgleish's site he
http://www.contextures.com/xlDataVal02.html where she shows how to set up dependent data validation. Basically you will have a list of items like ZCONNS, ZVIDCAB001 etc which you can choose from the first drop-down. Then by naming the ranges which these items link to, a second drop-down can automatically list the sub- divisions. If you have further data associated with, say, Bootlace Ferrule Violet 0.25mm, then you can use a VLOOKUP on this column. Hope this helps. Pete On Jul 28, 9:53*pm, Dudeone wrote: Thanks for the reply, yes your right regarding the Vlookup, what I think I need to do is use an IF statement but although ive used excel a lot its not been writing formulas. So I still need the Vlookup as I want to be able to find the item description and for it to populate a set row with said description. But in the formula I need it to be able to recognise ZCONNS and give me a list of all associated items and then allow the user to choose the correct item. Here is a further sample of our item numbers: ZVIDCAB001 * * *1694A Black ZVIDCAB002 * * *1694A Blue ZVIDCAB003 * * *1694A Red ZVIDCAB004 * * *1694A Breen ZCONNS *Bootlace Ferrule Violet 0.25mm ZCONNS *Bootlace Ferrule Pink 0.34mm ZCONNS *Bootlace Ferrule White 0.5mm ZCONNS *RJ11's - 4 contacts - STR ZCONNS *RJ11's - 4 contacts - Solid What I think I need is for the statement to read something like this: If E3 = ZCONNS then display list, Else ,VLOOKUP(E2,$B$3:C749,2,FALSE) However putting this in Excel is not as easy. "exoticdisease" wrote: Vlookup is the wrong function for you, it returns only the first value that it finds matching the one you lookup however many columns you specify, as you know. *To make a drop down list you can do a data validation: Date, validation, allow: list. *Press the cell selection button that has just appeared below the list option then choose the data that you want to be allowed (each cell chosen will represent a separate item on the list). *Then you can set whether your staff are allowed to input other values or not and what it says when they put in the wrong value etc. *If you want to do a vlookup function for this, you will need to use a unique value for each product, as that is how vlookup works. *At least, that's what I know! "Dudeone" wrote: Looking for some help, where I work the old system for booking out items from our store involved a pen and paper, ive now computerised this. I now have the staff booking out items, as I go I have been changing this to make it more user friendly and this has included using Vlookup so the staff only have to find the item number and the Vlookup completes the item description. =VLOOKUP(E2,$B$3:C749,2,FALSE) Ive now come to a brick wall, basically 99% of our stocked items are given unique stock numbers however we carry Consumable items (ZONNS), we have approx 30 different items all under ZCONNS. I have been surfing and reading articles but I cannot find the right formula that I need, what I would like to do is for a staff member to type in the item number ZCONNS and for it to then bring up a drop down list or similar for us to choose the correct item to book out of store. Below is a sample list of some of the consumables: ZCONNS * * * *Bootlace Ferrule Violet 0.25mm ZCONNS * * * *Bootlace Ferrule Pink 0.34mm ZCONNS * * * *Bootlace Ferrule White 0.5mm ZCONNS * * * *RJ11's - 4 contacts - STR ZCONNS * * * *RJ11's - 4 contacts - Solid ZCONNS * * * *RJ12's - 6 contacts - STR ZCONNS * * * *RJ12's - 6 contacts - Solid ZCONNS * * * *RJ45's - Stranded - STR ZCONNS * * * *RJ45's - Solid ZCONNS * * * *RJ45's - Shielded Stranded - STR ZCONNS * * * *RJ45's - *Shielded Solid * ZCONNS * * * *Resistors etc - All Sorts ZCONNS * * * *Cable Ties pkt100x(100x2.5) Natural ZCONNS * * * *Cable Ties pkt100x(100x2.5) Black Any help is greatly appreciated. Regards David- Hide quoted text - - Show quoted text - |
| All times are GMT +1. The time now is 08:10 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com