Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Neo1
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Gizmo63
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Neo1
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Gizmo63
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Neo1
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Gizmo63
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
vlookup problem puiuluipui Excel Discussion (Misc queries) 2 February 5th 06 06:46 PM
Vlookup problem with Date Time normajmarsh Excel Worksheet Functions 0 February 3rd 06 08:33 PM
vlookup Problem marksuza Excel Discussion (Misc queries) 3 December 22nd 05 04:40 PM
VLOOKUP Problem Tosca Excel Worksheet Functions 7 July 23rd 05 10:43 PM
VLOOKUP, OFFSET, MATCH PROBLEM, HELP? Steve Excel Worksheet Functions 0 January 30th 05 10:11 PM


All times are GMT +1. The time now is 06:43 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"