Help combining an iF statement with VLOOKUP

T

Tracy_J

New Member
Joined
Sep 16, 2013
Messages
2
Forgive my ignorance here guys, but I am lost!

I am attempting to input a formula to determine the rate charged for each room rental. My Initial worksheet looks like this. I need to input the rate into column C. The rate is determined by both column B and column E.

[TABLE="width: 421"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]ResNo[/TD]
[TD]Room#[/TD]
[TD]Daily Room Charge[/TD]
[TD]CustNo[/TD]
[TD]RateCode[/TD]
[/TR]
[TR]
[TD]R0010[/TD]
[TD]AL[/TD]
[TD][/TD]
[TD]C0001[/TD]
[TD]AD[/TD]
[/TR]
[TR]
[TD]R0010[/TD]
[TD]CA[/TD]
[TD][/TD]
[TD]C0001[/TD]
[TD]AD[/TD]
[/TR]
[TR]
[TD]R0011[/TD]
[TD]CO[/TD]
[TD][/TD]
[TD]C0002[/TD]
[TD]ST[/TD]
[/TR]
[TR]
[TD]R0012[/TD]
[TD]FL[/TD]
[TD][/TD]
[TD]C0003[/TD]
[TD]ST[/TD]
[/TR]
[TR]
[TD]R0017[/TD]
[TD]AL[/TD]
[TD][/TD]
[TD]C0005[/TD]
[TD]ST[/TD]
[/TR]
[TR]
[TD]R0017[/TD]
[TD]CA[/TD]
[TD][/TD]
[TD]C0005[/TD]
[TD]ST[/TD]
[/TR]
[TR]
[TD]R0017[/TD]
[TD]CO[/TD]
[TD][/TD]
[TD]C0005[/TD]
[TD]ST[/TD]
[/TR]
[TR]
[TD]R0017[/TD]
[TD]FL[/TD]
[TD][/TD]
[TD]C0005[/TD]
[TD]ST[/TD]
[/TR]
[TR]
[TD]R0017[/TD]
[TD]GA[/TD]
[TD][/TD]
[TD]C0005[/TD]
[TD]ST[/TD]
[/TR]
[TR]
[TD]R0003[/TD]
[TD]CA[/TD]
[TD][/TD]
[TD]C0006[/TD]
[TD]AD[/TD]
[/TR]
</tbody>[/TABLE]



I have created the lookup table below to determine the dollar amount of the rate.


[TABLE="width: 596"]
<colgroup><col><col><col span="3"></colgroup><tbody>[TR]
[TD]Room Code[/TD]
[TD]Meeting Room[/TD]
[TD]AD - Advance Rate[/TD]
[TD]ST - Standard Rate[/TD]
[TD]SP - Special Rate[/TD]
[/TR]
[TR]
[TD]AU[/TD]
[TD]Auditorium[/TD]
[TD="align: right"]$2,418.75[/TD]
[TD="align: right"]$2,925.00[/TD]
[TD="align: right"]$1,735.00[/TD]
[/TR]
[TR]
[TD]BD[/TD]
[TD]Boardroom[/TD]
[TD="align: right"]$1,912.50[/TD]
[TD="align: right"]$2,550.00[/TD]
[TD="align: right"]$1,330.00[/TD]
[/TR]
[TR]
[TD]CF[/TD]
[TD]Conference Center[/TD]
[TD="align: right"]$2,475.00[/TD]
[TD="align: right"]$3,200.00[/TD]
[TD="align: right"]$1,900.00[/TD]
[/TR]
[TR]
[TD]AL[/TD]
[TD]Alabama[/TD]
[TD="align: right"]$1,950.00[/TD]
[TD="align: right"]$2,300.00[/TD]
[TD="align: right"]$1,390.00[/TD]
[/TR]
[TR]
[TD]CA[/TD]
[TD]California[/TD]
[TD="align: right"]$1,987.50[/TD]
[TD="align: right"]$2,350.00[/TD]
[TD="align: right"]$1,422.50[/TD]
[/TR]
[TR]
[TD]CO[/TD]
[TD]Colorado[/TD]
[TD="align: right"]$1,968.75[/TD]
[TD="align: right"]$2,325.00[/TD]
[TD="align: right"]$1,375.00[/TD]
[/TR]
[TR]
[TD]FL[/TD]
[TD]Florida[/TD]
[TD="align: right"]$1,950.00[/TD]
[TD="align: right"]$2,300.00[/TD]
[TD="align: right"]$1,390.00[/TD]
[/TR]
[TR]
[TD]GA[/TD]
[TD]Georgia[/TD]
[TD="align: right"]$1,931.12[/TD]
[TD="align: right"]$2,275.00[/TD]
[TD="align: right"]$1,431.25[/TD]
[/TR]
</tbody>[/TABLE]


So basically what I need excel to do is say IF (on the 1st worksheet) column B equals AL AND column E equals AD THEN the result is cell C5 in my lookup table (on the second worksheet), but I have no idea how to do this.

Any suggestions?
Thanks!
~T
[TABLE="width: 421"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
Welcome to the Board!

If you change your rate codes in the rate table to AD, ST & SP to match the Rate Code on the room worksheet, then you can use something like this:


Excel 2012
ABCDE
1ResNoRoom#Daily Room ChargeCustNoRateCode
2R0010AL$1,950.00C0001AD
3R0010CA$1,987.50C0001AD
4R0011CO$2,325.00C0002ST
5R0012FL$2,300.00C0003ST
6R0017AL$2,300.00C0005ST
7R0017CA$2,350.00C0005ST
8R0017CO$2,325.00C0005ST
9R0017FL$2,300.00C0005ST
10R0017GA$2,275.00C0005ST
11R0003CA$1,987.50C0006AD
Rooms
Cell Formulas
RangeFormula
C2=VLOOKUP(B2,Rates!$A$2:$E$9,MATCH(Rooms!E2,Rates!$A$1:$E$1,0),0)
C3=VLOOKUP(B3,Rates!$A$2:$E$9,MATCH(Rooms!E3,Rates!$A$1:$E$1,0),0)
C4=VLOOKUP(B4,Rates!$A$2:$E$9,MATCH(Rooms!E4,Rates!$A$1:$E$1,0),0)
C5=VLOOKUP(B5,Rates!$A$2:$E$9,MATCH(Rooms!E5,Rates!$A$1:$E$1,0),0)
C6=VLOOKUP(B6,Rates!$A$2:$E$9,MATCH(Rooms!E6,Rates!$A$1:$E$1,0),0)
C7=VLOOKUP(B7,Rates!$A$2:$E$9,MATCH(Rooms!E7,Rates!$A$1:$E$1,0),0)
C8=VLOOKUP(B8,Rates!$A$2:$E$9,MATCH(Rooms!E8,Rates!$A$1:$E$1,0),0)
C9=VLOOKUP(B9,Rates!$A$2:$E$9,MATCH(Rooms!E9,Rates!$A$1:$E$1,0),0)
C10=VLOOKUP(B10,Rates!$A$2:$E$9,MATCH(Rooms!E10,Rates!$A$1:$E$1,0),0)
C11=VLOOKUP(B11,Rates!$A$2:$E$9,MATCH(Rooms!E11,Rates!$A$1:$E$1,0),0)


If you PM me your e-mail address I'll send you an example.

HTH,
 
Upvote 0
this is a bit more complicated than Smitty's answer, but does not require any reformatting of the spreadsheet in case that is not an option.

in column C
=IF(E2="AD",INDEX($C$16:$E$23,MATCH(B2,$A$16:$A$23),1),IF(E2="ST",INDEX($C$16:$E$23,MATCH(B2,$A$16:$A$23),2),INDEX($C$16:$E$23,MATCH(B2,$A$16:$A$23),3)))

The references are based on ResNo being at A1 and RoomCode being at A15, I copy/pasted what was in the post and removed a few lines...
Smitty's answer is quite a bit cleaner then the nested IF statements (personally I don't like nesting like this, but sometimes you have to).

There is another way to to it that is even simpler than either of these if format changes are an opt...
change "AD-..., ST-..., SP-..." to simply say "AD, ST, SP" and use this.

=INDEX($C$16:$E$23,MATCH(B2,$A$16:$A$23,0),MATCH(E2,$C$15:$E$15,0))
 
Last edited:
Upvote 0
Thanks! My lookup table is on a separate sheet, however. I have two sheets in my workbook. The first (with my reservation number data) is simply names "sheet 1" and contains 65 rows of information. The second sheet (with my lookup table) is named "Lookup Table" How do I get your formula to reference the lookup table on the second sheet?

Thanks!
~T


BausMan said:
this is a bit more complicated than Smitty's answer, but does not require any reformatting of the spreadsheet in case that is not an option.

in column C
=IF(E2="AD",INDEX($C$16:$E$23,MATCH(B2,$A$16:$A$23),1),IF(E2="ST",INDEX($C$16:$E$23,MATCH(B2,$A$16:$A$23),2),INDEX($C$16:$E$23,MATCH(B2,$A$16:$A$23),3)))

The references are based on ResNo being at A1 and RoomCode being at A15, I copy/pasted what was in the post and removed a few lines...
Smitty's answer is quite a bit cleaner then the nested IF statements (personally I don't like nesting like this, but sometimes you have to).

There is another way to to it that is even simpler than either of these if format changes are an opt...
change "AD-..., ST-..., SP-..." to simply say "AD, ST, SP" and use this.

=INDEX($C$16:$E$23,MATCH(B2,$A$16:$A$23,0),MATCH(E2,$C$15:$E$15,0))
Click to expand...
 
Upvote 0
Tracy_J said:
Thanks! My lookup table is on a separate sheet, however. I have two sheets in my workbook. The first (with my reservation number data) is simply names "sheet 1" and contains 65 rows of information. The second sheet (with my lookup table) is named "Lookup Table" How do I get your formula to reference the lookup table on the second sheet?

Thanks!
~T
Click to expand...

Probably the easiest way is using named ranges. In the MATCH statement, for instance, the named range would replace the array that is being searched. The same is true for the INDEX functions. If the price table was A1:C3 then if you name that price_table and make it workbook, not sheet specific, than anytime you need that range you just need to call its name, from any sheet in the spreadsheet. Sorry I can't give some examples right now, but I am away from the office with my iPad..hope that is enough to get you started though.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Help with FilterXML formula
Replies
0
Views
2K
montex01
M
M
  • Question Question
VLOOKUP from a range matching multiple criteria
Replies
3
Views
772
merce333
M
L
  • Question Question
Trying to create a mini rating system
Replies
4
Views
571
njimack
N
H
  • Question Question
Permutation list help needed...totally lost
Replies
2
Views
495
hyprlteman
H
D
  • Question Question
URGENT: Help with counting how often a value occurs
Replies
6
Views
529
hiker95
H

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top