Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup problem..
Hello I have a slight problem creating a vlookup, I've done a list of Hotels and beside that cell i want to create a Vlookup basically I'm trying to do that following: I a drop down list with the following hotels: Hotel * Hotel ** Hotel *** Hotel **** and then beside that drop down list cell, i have a vlookup which is like this =VLOOKUP(A1,M20:N27,2,false) But obviously it doesnt work since excel doesnt recognise the * it only recognises the name which is hotel and since they are all the same it displays the same price which that isnt the case in my list of Hotels and prices...how can i deal with this problem...could i use another sort of star or....? Thanks for your time From John -- Neo1 ------------------------------------------------------------------------ Neo1's Profile: http://www.excelforum.com/member.php...o&userid=30329 View this thread: http://www.excelforum.com/showthread...hreadid=522635 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup problem..
Hi,
Your simplest solution is to use a modified list, say - Hotel 1* Hotel 2* Hotel 3* etc If you have to use the ** or *** or **** in output I'd be inclined to do a little jiggery pokery at the output stage to to convert the 3 to ***. hth Giz "Neo1" wrote: Hello I have a slight problem creating a vlookup, I've done a list of Hotels and beside that cell i want to create a Vlookup basically I'm trying to do that following: I a drop down list with the following hotels: Hotel * Hotel ** Hotel *** Hotel **** and then beside that drop down list cell, i have a vlookup which is like this =VLOOKUP(A1,M20:N27,2,false) But obviously it doesnt work since excel doesnt recognise the * it only recognises the name which is hotel and since they are all the same it displays the same price which that isnt the case in my list of Hotels and prices...how can i deal with this problem...could i use another sort of star or....? Thanks for your time From John -- Neo1 ------------------------------------------------------------------------ Neo1's Profile: http://www.excelforum.com/member.php...o&userid=30329 View this thread: http://www.excelforum.com/showthread...hreadid=522635 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup problem..
Yea true i could do it that way, so there isnt any way if I had the stars alone? it would involve programming i guess? Thanks From John -- Neo1 ------------------------------------------------------------------------ Neo1's Profile: http://www.excelforum.com/member.php...o&userid=30329 View this thread: http://www.excelforum.com/showthread...hreadid=522635 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup problem..
TBH there may be a complicated way. As mentioned, I'd just tweak the output
cell so wherever the selection was reported could read: ="Hotel "&CHOOSE(MID(A3,7,1),"*","**","***","****","*****" ,"ERROR") Where A3 is the referencing cell, the "ERROR" is just an overflow trap I always use with 'CHOOSE'. Giz "Neo1" wrote: Yea true i could do it that way, so there isnt any way if I had the stars alone? it would involve programming i guess? Thanks From John -- Neo1 ------------------------------------------------------------------------ Neo1's Profile: http://www.excelforum.com/member.php...o&userid=30329 View this thread: http://www.excelforum.com/showthread...hreadid=522635 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup problem..
Sorry I'm completely lost in the Choose what you did there...what do you mean? Thanks a lot From John -- Neo1 ------------------------------------------------------------------------ Neo1's Profile: http://www.excelforum.com/member.php...o&userid=30329 View this thread: http://www.excelforum.com/showthread...hreadid=522635 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup problem..
From your original post the constant part of the value will be "Hotel".
A3 is the cell I've used as containing the output from the dropdown (i.e. "Hotel 3*" From the suggested way of showing the star rating the 7th character will be the number representing the 'stars'. I've extracted the 7th character - mid(a3,7,1) and used this as the index for the CHOOSE function. In the above example the 7th character is 3, so the 3rd CHOOSE option is used in the text join so the final expression will be "Hotel ***" If the index is greater than the number of options in the CHOOSE statement it returns the last value, so my own habit is to add "ERROR" into most CHOOSE statements to indicate an overflow. So should you end up (somehow?!?) with "Hotel 6*" the final output would be "Hotel ERROR". If you need more help with CHOOSE check out the Excel Help file. With a little work you can make it more flexible so instead of just showing "Hotel ***" you could use the actual hotel names. hth Giz "Neo1" wrote: Sorry I'm completely lost in the Choose what you did there...what do you mean? Thanks a lot From John -- Neo1 ------------------------------------------------------------------------ Neo1's Profile: http://www.excelforum.com/member.php...o&userid=30329 View this thread: http://www.excelforum.com/showthread...hreadid=522635 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup problem | Excel Discussion (Misc queries) | |||
Vlookup problem with Date Time | Excel Worksheet Functions | |||
vlookup Problem | Excel Discussion (Misc queries) | |||
VLOOKUP Problem | Excel Worksheet Functions | |||
VLOOKUP, OFFSET, MATCH PROBLEM, HELP? | Excel Worksheet Functions |