Help combining an iF statement with VLOOKUP

[Tracy_J]

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.

ResNo	Room#	Daily Room Charge	CustNo	RateCode
R0010	AL		C0001	AD
R0010	CA		C0001	AD
R0011	CO		C0002	ST
R0012	FL		C0003	ST
R0017	AL		C0005	ST
R0017	CA		C0005	ST
R0017	CO		C0005	ST
R0017	FL		C0005	ST
R0017	GA		C0005	ST
R0003	CA		C0006	AD

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

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

Room Code	Meeting Room	AD - Advance Rate	ST - Standard Rate	SP - Special Rate
AU	Auditorium	$2,418.75	$2,925.00	$1,735.00
BD	Boardroom	$1,912.50	$2,550.00	$1,330.00
CF	Conference Center	$2,475.00	$3,200.00	$1,900.00
AL	Alabama	$1,950.00	$2,300.00	$1,390.00
CA	California	$1,987.50	$2,350.00	$1,422.50
CO	Colorado	$1,968.75	$2,325.00	$1,375.00
FL	Florida	$1,950.00	$2,300.00	$1,390.00
GA	Georgia	$1,931.12	$2,275.00	$1,431.25

<colgroup><col><col><col span="3"></colgroup><tbody>
</tbody>

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

<tbody>
</tbody>

 

[Smitty]

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
	A	B	C	D	E
1	ResNo	Room#	Daily Room Charge	CustNo	RateCode
2	R0010	AL	$1,950.00	C0001	AD
3	R0010	CA	$1,987.50	C0001	AD
4	R0011	CO	$2,325.00	C0002	ST
5	R0012	FL	$2,300.00	C0003	ST
6	R0017	AL	$2,300.00	C0005	ST
7	R0017	CA	$2,350.00	C0005	ST
8	R0017	CO	$2,325.00	C0005	ST
9	R0017	FL	$2,300.00	C0005	ST
10	R0017	GA	$2,275.00	C0005	ST
11	R0003	CA	$1,987.50	C0006	AD
Rooms

Cell Formulas
Range	Formula
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,

 

[BausMan]

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))

 

[Tracy_J]

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

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))

 

[BausMan]

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

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.