VLOOKUP Help
Hey all,
So I have a database that I maintain in which I need to create a VLOOKUP that looks at a cell that is a drop down selected by the user to help narrow their search and then input data into another cell. There's about 4 or 5 items in the drop down list to choose from. When the user selects one of the items in the drop down what I want the VLOOKUP formula to do is look at only at a certain range of cells in the index that matches the item in the drop down.
For example the drop down is to select a device and then the user inputs the service group into another cell and then information about the device with that service group is displayed in cells below it.
How would I build a formula to accomplish this?
Thanks in advance!
Last edited by NBVC; 11-26-2011 at 08:56 AM.
Can you name each section of the lookup table to match the name in the dropdown list?
Can you also post a sample to better describe what you are looking for.. you can attach a sample spreadsheet directly.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Can you name each section of the lookup table to match the name in the dropdown list?
Can you also post a sample to better describe what you are looking for.. you can attach a sample spreadsheet directly.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Here's an example workbook. I removed the proprietary data, but it allows you to see what I'm talking about.
Can you give example of possible inputs and expected results (and where they come from exactly)?
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
I reuploaded the example worksheet and input some fake data. The data is being input on the CMTS NCP Index tab. What I want to do is in the gray cells the user picks one of the CMTS's from the drop down and then types in the number of the service group ID they want to look up and then it displays the properties for the service group on that CMTS the user chose.
What if ALIACMTK01 is selected. Is there another row for Service Group 66?
Are the lookups going to be based on columns B and H in the CMTS NCP Index sheet?
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Yes, essentially the list will be arranged numerically by the Service Group number and the CMTS in column H.
So for example say we had service groups 01, 02, 03, 04 for ALIACMTK01 and then service groups 01, 02, 03, 04 for CENTCMTK01.
The list would be arranged so that it looked like this (omitting the other fields)
Row#6 01 ALIACMTK01
Row#7 02 ALIACMTK01
Row#8 03 ALIACMTK01
Row#9 04 ALIACMTK01
Row#10 01 CENTCMTK01
Row#11 02 CENTCMTK01
Row#12 03 CENTCMTK01
Row#13 04 CENTCMTK01
So when a a user selects ALIACMTK01 from the drop down list then the VLOOKUP would only look at the data in rows 6-9 and when the user selects CENTCMTK01 from the drop down list the VLOOKUP looks at only the data in rows 10-13.
What I would suggest then, is to add a helper formula in column AI of the CMTS NCP Index sheet. At AI4 enter formula: =H4&"_"&B4 this concatenates the 2 main columns.
Then in say in the Node # box in the CMTS Service Group ID Lookup sheet enter:
=INDEX('CMTS NCP Index'!C:C,MATCH($F$4&"_"&$F$7,'CMTS NCP Index'!$AI:$AI,0))
do the same in other boxes changing the C:C to match the corresponding columns for the item to retrieve.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Perhaps you can tell me if I'm doing something wrong in the formula. All it keeps returning is #VALUE!. At the end of my data for the first entry I put =C4&"_"&K4 in cell AN4 (this is where they're actually at on the real database) and that works as it displays "01_ALIACMTK01"
The example below is for looking up the data in the Split Node column which is column #7 in the lookup. I've only entered a single row of data to try to get it work. Can you tell me if this is correct? Or perhaps edit the example workbook I used so that I might be able to compare?
=INDEX('CMTS NCP Index'!C4:I4,MATCH('CMTS Service Group NCP Lookup'!F4:G4&"_"&'CMTS Service Group NCP Lookup'!F7:H8,'CMTS NCP Index'!AN4,7))
Why not keep it like mine, and change the AI to AN?
=INDEX('CMTS NCP Index'!C:C,MATCH($F$4&"_"&$F$7,'CMTS NCP Index'!$AN:$AN,0))
in this format you don't need to include the column number.. just change the C:C to match the column of interest...
or if you must use column...
=INDEX('CMTS NCP Index'!$C:$AN,MATCH($F$4&"_"&$F$7,'CMTS NCP Index'!$AI:$AI,0),1)
where 1 is column number (column C here).
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Hmm I'm not sure where we're going wrong on this one. It's now changed from #VALUE! to #N/A. I uploaded a copy of just the database I'll be working with if you want to take a look and point out what I should be doing differently. Thanks again for your help on this by the way.
This is the formula for Node #:
=INDEX('CMTS NCP Index'!C:N,MATCH('CMTS Service Group NCP Lookup'!F7&"_"&'CMTS Service Group NCP Lookup'!F4,'CMTS NCP Index'!$AN:$AN,0),2)
this is the formula for Node Location:
=INDEX('CMTS NCP Index'!C:N,MATCH('CMTS Service Group NCP Lookup'!F7&"_"&'CMTS Service Group NCP Lookup'!F4,'CMTS NCP Index'!$AN:$AN,0),3)
the red, bold number is all you need to change to correspond to the column number of interest you want to get.
Note: Working with merged cells is not recommended as it makes all this difficult. You have ot paste the formulas in the formula bar to work.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Awesome! Just checked it out and it's working perfectly! Thanks for helping out it is much appreciated!
Title comes with VLOOKUP problem, seems that the only possible way, but solution will go with INDEX function, interesting
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks