isna, vlookup, range

[Alby]

Hi all

this is a tricky one, for me at least

I have a table (sheet1)
A B C D E F G
LEG BASIC 0-100 101-250 251-500 501-1000 1001+ MINIMU

MEL - SYD 10.00 .50 .45 .40 .35 .27 15.0
MEL - BNE 8.00 .80 .76 .65 .45 .35 10.0

I also have a range of data (sheet2)

A B
LEG Kilos Cos

MEL -SYD 52
MEL- SYD 12
MEL- BNE 60

In C2 and down I need to calculate the cost of the legs. So
VLOOKUP ( MEL-SYD, $A$2:$H$50,2,0) +VLOOKUP ( MEL-SYD, $A$2:$H$50,the range b2 on the 2nd sheet falls in(CDEFG),0) and if the value of this argument doesnt equal or exceed the H column then it equals the H column
I've got this far, but am struggling wit the differing weight range

=if(IF(ISNA(VLOOKUP(A2,ABC,2,FALSE)+VLOOKUP(A2,ABC,3,FALSE)*B2),"",(VLOOKUP(A2,ABC,2,FALSE)+VLOOKUP(A2,ABC,3,FALSE)*B2))<H2,H2,=IF(ISNA(VLOOKUP(A2,ABC,2,FALSE)+VLOOKUP(A2,ABC,3,FALSE)*B2),"",(VLOOKUP(A2,ABC,2,FALSE)+VLOOKUP(A2,ABC,3,FALSE)*H2)

Any Ideas

Alb

 

[Bob Phillips]

Alby,

Here is a formula for the basic + weight differential

=IF(NOT(ISNA(MATCH(A6,A2:A3,0))),VLOOKUP(A6,A2:B3,2,FALSE)+INDEX(A2:H3,MATCH
(A6,A2:A3),MATCH(B6,{0,100,250,500,1000,9999})+2),"")

This assumes that the sheet2 data is in A6 & B6, and the table on sheet1 is
A2:H3 - you will need to adapt.

Not clear where minimum (column H) comes in

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Hi all,

this is a tricky one, for me at least!

I have a table (sheet1):
A B C D

E F G H

LEG BASIC 0-100 101-250 251-500

501-1000 1001+ MINIMUM

MEL - SYD 10.00 .50 .45 .40

..35 .27 15.00

MEL - BNE 8.00 .80 .76 .65

..45 .35 10.00

I also have a range of data (sheet2):

A B C
LEG Kilos Cost

MEL -SYD 52
MEL- SYD 126
MEL- BNE 600

In C2 and down I need to calculate the cost of the legs. So :
VLOOKUP ( MEL-SYD, $A$2:$H$50,2,0) +VLOOKUP ( MEL-SYD, $A$2:$H$50,the

range b2 on the 2nd sheet falls in(CDEFG),0) and if the value of this
argument doesnt equal or exceed the H column then it equals the H column.

 

[Frank Kabel]

Hi
try the following:
1. Change the layout of your lookup sheet (only use the lower value of
your boundary):
A B C D ...
1 LEG BASIC 0 101 ...
2 ... ...
3

Now use the following formula on your second sheet (e.g. in cell C2)=
=MAX(VLOOKUP(A2,'sheet1'!$A$1:$B$50,2,0)+INDEX('sheet1'!$A$1:$G$50,MATC
H(A1,'sheet1'!$A$1:$A$50,0),MATCH(B1,$A$1:$G$1,1)),VLOOKUP(A2,'sheet1'!
$A$1:$H$50,8,0))

 

[Frank Kabel]

Hi
forgot the weight in the formula and also messed the referencese. Use
=MAX(VLOOKUP(A2,'sheet1'!$A$1:$B$50,2,0)+B2*INDEX('sheet1'!$A$1:$G$50,M
ATC
H(A2,'sheet1'!$A$1:$A$50,0),MATCH(B2,$A$1:$G$1,1)),VLOOKUP(A2,'sheet1'!
$A$1:$H$50,8,0))

 

[Norman Harker]

Hi Alby!

I've re-structured your sheet1 table a little:

A1:H3
Leg Basic 0 100 250 500 1000 Minimum
MEL-SYD 10 0.5 0.45 0.4 0.35 0.27 15
MEL-BNE 8 0.8 0.76 0.65 0.45 0.35 10


In Sheet2

A1:C4
Kilos Cost
Mel-Syd 52 36
Mel-Syd 126 66.7
Mel-Bne 600 278


C2 formula is:
=IF(A2="MEL-SYD",MAX(HLOOKUP(B2,Sheet1!$C$1:$G$3,2)*B2+Sheet1!$B$2,Sheet1!$H$2),IF(A2="MEL-BNE",MAX(HLOOKUP(B2,Sheet1!$C$1:$G$3,3)*B2+Sheet1!$B$3,Sheet1!$H$3),""))

It seems to work out OK but I may be interpreting the data
incorrectly.

If you add to your legs I think that inserting leg numbers that match
the row number might avoid having complex IF functions because you
could determine the row number looked up by using the leg number.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Alby]

Thanks Bob, Frank and Norma

 

[Alby]

Ok Bob, Frank and Norman

I've tested your responses and the If statement can't work as there could be up to 1000 different legs
I can follow this formula to some degree but I probably didn't make myself clear. There doesn't seem to be a multiplier for the kilos and weight rate.....Mel-Syd should be 50(kgs) * .52 + 10 = 3

=IF(NOT(ISNA(MATCH(A6,A2:A3,0))),VLOOKUP(A6,A2:B3,2,FALSE)+INDEX(A2:H3,MATC
(A6,A2:A3),MATCH(B6,{0,100,250,500,1000,9999})+2),""

Also would it be possible to put another IF statement in front of this to accomodate the minimums

Thanks again guys.......I don't know what I'd do without you. I've learnt so much by just reading your responses and have even responded to others posts. 3 months ago I could never have done that. Thank

Alby

 

[Frank Kabel]

Hi Alby
see my second reply. ths includes the multiplier as well as your
minimum value

 

[Alby]

Frank
I keep getting "error in formula message

=MAX(VLOOKUP(A3,Sheet1!$A$1:$B$50,2,0))+B3*INDEX(Sheet1!$A$1:$G$50,MATCH(A3,Sheet1!$A$1:$G$50,0),MATCH(B3,Sheet1!$A$1:$G$1,1)),VLOOKUP(A3,Sheet1!$A$1:$H$50,8,0)

tried breaking it up
=MAX(VLOOKUP(A3,Sheet1!$A$1:$B$50,2,0)) returns 10.00 basic o
+B3*INDEX(Sheet1!$A$1:$G$50,MATCH(A3,Sheet1!$A$1:$G$50,0) not sure here, does the index require a reference to A3
The MATCH is looking for the row header to match kg
MATCH(B3,Sheet1!$A$1:$G$1,1)), seems to be matching kgs again. the single 1 denotes what
VLOOKUP(A3,Sheet1!$A$1:$H$50,8,0)) is looking for the minimum col 8

There doesn't seem to be an IF statement to say that if all the other arguments are less than the H col then the answer will be H

Thanks again Fran

Alby

 

[Norman Harker]

Hi Alby!

Always pleased to help. If you get problems extending your "legs",
don't hesitate to post for help.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Frank Kabel]

Hi Alby
messed the brackets.
=MAX(VLOOKUP(A3,Sheet1!$A$1:$B$50,2,0))+....

make this (as in my previous example)
=MAX(VLOOKUP(A3,Sheet1!$A$1:$B$50,2,0)+...

So in total use

=MAX(VLOOKUP(A3,Sheet1!$A$1:$B$50,2,0)+B3*INDEX(Sheet1!$A$1:$G$50,MATCH
(A3,Sheet1!$A$1:$G$50,0),MATCH(B3,Sheet1!$A$1:$G$1,1)),VLOOKUP(A3,Sheet
1!$A$1:$H$50,8,0))


The MAX statement should take care that clumn H is used then the other
part is below that value

 

[Alby]

Frank

Still contains an error in formula

Alby

 

[Frank Kabel]

Hi
if you like emial me your file
frank[dot]kabel[at]freenet[dot]de

 

[Frank Kabel]

Hi
file is on the way to you. It was a missing sheet name in your formula.

--
Regards
Frank Kabel
Frankfurt, Germany

Hi
if you like emial me your file
frank[dot]kabel[at]freenet[dot]de

Frank,

Still contains an error in formula!

Alby