V Lookup Help

[nmarshall]

Can Anybody Help Me with the Following VLookup Formula:

=IF(E8="Bank hol",VLOOKUP(D8,Sheet4!$H$1:$I$12,2,FALSE)*1.5,VLOOKUP(D8,Sheet4!$H$1:$I$12,2,FALSE))

I need To remove the Annoying #N/A When nothing is entered in some of the cells, and allow it to carry on working out.

Thanks

 

[Sandeep Warrier]

Hi & welcome to the Board!

When nothing is entered in which cells?

Looking at your formula I'm guessing that you will get #N/A if VLOOKUP does not find D8 in the lookup range (Sheet4 H1:I12). If this is the case then try replacing your VLOOKUP formula with

IF(ISNUMBER(MATCH(D8,Sheet4!$H$1:$I$12,0),VLOOKUP(D8,Sheet4!$H$1:$I$12,2,FALSE),"Value  Not Found")

If you use Excel 2007 or later then try

=IFERROR(VLOOKUP(D8,Sheet4!$H$1:$I$12,2,FALSE),"Value Not Found")

<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden"><input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden"><input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">

 

[nmarshall]

I have a Drop Down list In Cell D8, And when one of the Items from the List Is Selected, it Looks at E8 To see if it is a normal day or a Bank Holiday, and If it is a Normal Day or Nothing is entered then it puts a Price in Another Cell of $40

 

[nmarshall]

Your Second Formula Works Great, But what I want it to do is Basically When I pick from my drop down Box in D8 For Instance "Business" and then in E8 Choose "Normal" or "Bank Holiday" Day Type then I want it to put the Value in Cell "I8" Of $40 For Business and $45 for IAC And 37.5 For Education and then X1.5 Each Rate if Bank holiday from Cell E8 Is Selected.

I hope this makes Sense. I can send you a copy of the spreadsheet if that would Help.

Thanks Neil

 

[Sandeep Warrier]

Hi,

You can post some sample data on the board using any one of the following ways.

1. Excel Jeanie
2. Richard Scholar's HTML Maker
3. If you are using Internet Explorer then put borders around the cells in question, copy the range and paste it here directly.
<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden"><input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">

 

[nmarshall]

Can I not E-Mail to you. I am using Excel on Mac OS'x and do not have a converter installed

 

[Sandeep Warrier]

Try step 3 from my last post... it might work for Safari.
<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">

 

[nmarshall]

Its too Wide a Spreadsheet to Show you What I Need. I will Try, Here You Go.

Cust Type Day type Client Callout Unit Price
Business Normal 40
Education Normal 37.5
Education Normal 37.5
Business Normal 40
Business Normal 40
Education Normal 37.5
Business Normal 40
Education Normal 37.5

When I Select Cust Type As Either Business, Education Or IAC, Then It will Put the Figures $40, $37.5 or $45 In to the Unit Price. I also want to be able to select Day Type as Normal or Bank Holiday or Weekend and it will know that a Normal Day is a Normal Price but a Holiday or Weekend the Unit Price then Gets Multiplied by 1.5

The Formula you gave me, the second one works Great but doesn't Multiply the prices by 1.5 depending on What Day type is selected.

Thanks

Neil

 

[Sandeep Warrier]

Probably something like

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Cust Type	Day Type	Unit Price					Lookup Table
2	Business	Normal	40					Cust Type	Unit Price
3	Education	Normal	37.5					Business	$40
4	IAC	Normal	45					Education	$37.50
5	Business	Holiday	60					IAC	$45
6	Education	Holiday	56.25
7	IAC	Holiday	67.5
8	Business	Weekend	60
9	Education	Weekend	56.25
10	IAC	Weekend	67.5
Sheet1

Excel 2007

Cell Formulas
Range	Formula
C2	=IF(B2="Normal",IFERROR(VLOOKUP(A2,$H$3:$I$5,2,FALSE),"Value Not Found"),IFERROR(VLOOKUP(A2,$H$3:$I$5,2,FALSE)*1.5,"Value Not Found"))

<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">

 

[nmarshall]

Thanks,
It works apart from when a Bank Holiday Is Selected, It is not Multiplying by 1.5

Here is the one from my spreadsheet which I modified from Yours.

=IF(E12="Normal",IFERROR(VLOOKUP(D18,Sheet4!$H$1:$I$12,2,FALSE),"0"),IFERROR(VLOOKUP(D18,Sheet4!$H$1:$I$12,2,FALSE)*1.5,"0")