Jump to content


 


Register a free account to unlock additional features at BleepingComputer.com
Welcome to BleepingComputer, a free community where people like yourself come together to discuss and learn how to use their computers. Using the site is easy and fun. As a guest, you can browse and view the various discussions in the forums, but can not create a new topic or reply to an existing one unless you are logged in. Other benefits of registering an account are subscribing to topics and forums, creating a blog, and having no ads shown anywhere on the site.


Click here to Register a free account now! or read our Welcome Guide to learn how to use this site.

Photo

Excel Vlookup


  • Please log in to reply
3 replies to this topic

#1 splackavellie

splackavellie

  • Members
  • 133 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Atlanta
  • Local time:06:11 PM

Posted 23 March 2006 - 07:26 PM

ok i dont really know how to pose my question so i'll just describe a situation very similar to it to simplify it a little.

i have 3 list (Subdivision, Plan, and Price) in a 2nd sheet (but same file) called "Homes". In the main sheet i created a dropdown list for all subdivisions. then the 2nd dropdown list will list only plans in that subdivisions. once you have chosen a subdivision and a plan from the dropdown lists, then the price of that plan will be shown on another cell.

It works fine until we have plans with the same name in different subdivisions. it seems the it will only look up the first plan name that it comes across.

example: i have a plan called plan_A in both Subdivision sub_A and sub_B. if i choose sub_A from the dropdown list and then plan_A, the price for plan_A in sub_A will be displayed. but if i choose sub_B and plan_A, the price of plan_A from sub_A is still being displayed b/c its the first one in my table.

is it possible to have it so that it looks up both the subdivision and the plan? we tried to put the diff subdivisions in different sheets, but then we dont know how to use vlookup so that it will look at the sheet specified by the first dropdown list.

BC AdBot (Login to Remove)

 


#2 groovicus

groovicus

  • Security Colleague
  • 9,963 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Centerville, SD
  • Local time:04:11 PM

Posted 23 March 2006 - 11:37 PM

AS far as I recall, vloolup and hlookup will only look for a match to a single cell. I did a spreadsheet awhile ago that I ran into that same issue, and I had to use unique names. I also had instances where if the names were similar enough, it would get confused.

One can do alot with spreadsheets, but they are pretty rudimentary. I don't know how excel handles conditionals. If it can do matches, and you know it is always going to stop at the first instance of the subdivision, you may be able to walk through until you find a match..... I know one can do conditional formatting, and I know one can do conditional checks for data, but I don't know how many can be strung together.

#3 splackavellie

splackavellie
  • Topic Starter

  • Members
  • 133 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Atlanta
  • Local time:06:11 PM

Posted 24 March 2006 - 06:04 PM

how about using vlookup to match a choice in the dropdown list? say i put the different subdivisions in different sheets. that way there wont be plans with the same name in the same sheet. the only problem is i dont know how to use vlookup so that it will look at the sheet that corresponds to the subdivision that was picked.

#4 tos226

tos226

    BleepIN--BleepOUT


  • Members
  • 1,578 posts
  • OFFLINE
  •  
  • Gender:Female
  • Location:LocalHost
  • Local time:05:11 PM

Posted 04 April 2006 - 10:56 PM

Yes there is a way to lookup on composite criteria (subdivision+plan), but it requires an extra field. I've done it.
I'm just copying one cell from a spreadsheet I made so long ago, I'll probably miss some details:
=VLOOKUP($C39&$D39,table2,6,FALSE)
(I ommited all the code which takes care of 'what if no match' to keep it simple here)
I'm looking in "table2" for a match on two cells c39 and d39 (your subdivision and plan).
Table2 lookup value is a composite of two items made by concatenation.
So if your two items are SubdivisionA (in C39) and PlanA (inD39), the composite would be unreadable SubdivisionAPlanA you'd put in the first column of what I named table2 above, but Excel doesn't mind if you can't read it :thumbsup:

Regarding tables on a different sheet - just qualify the names with sheet name in front of a specific location or a named reference. The syntax is SheetName!cell (or named range)

Regarding what VLOOKUP() finds or doesn't, from Excel Help:

Remarks
If VLOOKUP can't find lookup_value, and range_lookup is TRUE, it uses the largest value that is less than or equal to lookup_value.
If lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP returns the #N/A error value.
If VLOOKUP can't find lookup_value, and range_lookup is FALSE, VLOOKUP returns the #N/A value.

For EXACT lookup, use the range parameter = FALSE

Hope this helps a bit.

Edited by tos226, 04 April 2006 - 11:13 PM.





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users