Formula to calculate price for hotel room upgrades

Hi all!
Working on a sheet that will do automatic upgrades of hotel rooms .
SO my chart looks like this:
A B C D E
1Original room Category Desired room Categorie Price
2112 E 345 A ?

So I have made a chart (on a different worksheet) with all the categories and their prices that looks like this:
A B C D E F G .....
Cat E D C B A
1 E 0 90 100 134 200
2 D 90 0 10 44 110
3 C 100 0 0 .......
And so on.

So I need to find a formula that will look for the original category to the one I want to upgrade to and tell me the price.

Any suggestions? Can somebody help? Thanks!

While it may be possible for having a solution for the given sample (I am not trying for one), I suggest to redesign the second sheet like
Orginal_Category_To_Revised_Catogory Price
EtoD,90
EtoC,100
EtoB,134
EtoA,200

In the first sheet you can VLOOKUP by doing a concatenation for Original room Category & "to" & Desired room Categorie.

Hi and welcome to the forum

We would love to help you with your question, but 1st, please rename your thread to something more meaningful, that actually describes your problem. Many members will look at a thread title, and if it is of interest to them, or falls within their area of expertise, they might only open those threads. also, searching for "help" will not turn up too many results that will benefit you, sorry.

Look at it this way...if you typed that title into google, what would you expect to get back?
To change a Title on your post, click EDIT on you're 1st post, then Go Advanced and change your title

Hi uplate, I built an intersection table showing the various prices and room types and included a formula to show the price difference for each upgrade/downgrade. You can expand the chart by adding the new room type and price to both the column headers and the row headers. So if you have 4 room types, your table would need to be 4*4 in order to show all possible moves. You reference the table by vlookup, but use the new room type name instead of the column number. =VLOOKUP(A2,$T$5:$Z$10,MATCH("COLUMN_NAME",$T$4:$Z$4,0),0) is an example of a way you can use a string to make a vlookup with a column header instead of the column number.

Sample.xlsx

@ wraithlet & Uplate...PLEASE read forum rules..
Uplate Please comply with FDibbins post (#3)

Rule 1

..1. Use concise, accurate thread titles. Your post title should describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change the title of the thread, click EDIT on the original post, then click the Go Advanced button, then change the title. If two or more days have passed, the EDIT button will not appear, and you need to ask a moderator to change the title.

Rule 7

.

.7. Don't ignore requests by Administrators, Moderators, or senior members of the forum. If you are unclear about their request or instructions, then send a private message to them asking for help. Do not post a reply in a thread where such a request (e.g., title change, code tags) is pending.

Thanks Fotis and dredwolf.

Wraithlet,

Please change your thread title as per post 3.

@ Uplate.

We are here to help people. Do you think that i have 5940 posts here, without reason? Forum has some rules that everyone must obey. I write these because of your pm.

..Hi,

Did you read post #4?
In my post i ask for a suggested titel as i dont understan much about excel and dont eant to use the wrong working.
I have not ignored in ANY way what it has been told to me on post #3.
Have i broken any other rule or offended anyone? Because I dont understand all the hostility there is in this forum.

A Moderator or our Admin that is online now, maybe will want to explain something more to you.

Good luck

Fotis,

Ideally you shouldnt post the content from PMs here. Also, the OP didnt understand how to rephrase the title. I assisted them via PM. And now everything is sorted.

Sorry but i don't agree this time with you Arlette.

I usually have more than 5 PM's per day and of course never done this again. This was not a Pm regarding any private issues. It was a pm just for forum rules issue. So, for me, is not private. It is a public issue.

@ Uplate:

If you upload a small sample workbook will be easier for us to suggest a solution for your problem.

Maybe for a bit of clarity?

In post #3 I asked for the name change.
post #4 was unchanged
post #5 was changed
posts #6-10 were again unchanged
Post # 11 onwards, changed again

It looks like uplate changed the title (at 1st) for just#5, and then changed it for the tread in post 11?

I went back and manually changed the title of my posts, is that something that each post author would need to do?

You only do it in your FIRST post,(SHOULD only be able to there), IF it meets a moderators approval,(they will remove their request if it does) THEN answers can be posted...I think what is confusing people, is that it looks like you changed it 2-3 times????