Vlookup x 2 +#N/A and IF statement

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all

Hoping someone can help me

I have a named range: Nort
A B
Melbourne 10 .
Sydney 8 .
Et

In another spreadsheet I have 5000+ transaction

Reads like:
A
Dest kg
Melbourne 2

Not all the destinations are covered in the named range. So I need to Vlookup "Melbourne" and Multiply24 by .5 then add 10

Further to that, there is also a minimum of 15 per transaction and the problem of the ugly #N/

In essence I need something Like

=IF(VLOOKUP(A2,North,2,false)+VLOOKUP(A2,North,3,false)*B2<15,15,VLOOKUP(A2,North,2,false)+VLOOKUP(A2,North,3,false)*B2

but I also need to get rid of th #N/A'

Thanks Alby
 
Perhaps try something along these lines:

In Sheet1
---------
Assume your set-up is as below
in cols A, B and C
with data in row1 down

Melbourne...10.....0.5
Sydney.......8.....0.4
etc

In Sheet2
---------
Assume your set-up is as below
in cols A and B,
with data from row2 down:
A B
Dest kgs
Melbourne 24
Click to expand...

Put in C2:

=IF(ISNA(MATCH(TRIM(Sheet2!A2),Sheet1!$A:$A,0)),"",MAX(SUM
(OFFSET(Sheet1!$A$1,MATCH(TRIM(Sheet2!A2),Sheet1!$A:$A,0)-
1,2)*B2,OFFSET(Sheet1!$A$1,MATCH(TRIM(Sheet2!A2),Sheet1!
$A:$A,0)-1,1)),15))

Copy C2 down as many rows as there is data in col A

The hardcoded "15" near the end of the formula is taken to
be your minimum condition of 15 per transaction. You can,
if you wish, change this "15" to be a cell reference
instead, say "Z1" for greater flexibility, if the minimum
is something which may also change in time.

If I've read your specs correctly, col C will return what
is required.

But do perform some checks on your own to confirm this
(input some test data with known results)

--
Rgds
Max
xl 97
---
Please respond, in newsgroup
xdemechanik <at>yahoo<dot>com
----

Alby said:
Hi all,

Hoping someone can help me.

I have a named range: North
A B C
Melbourne 10 .5
Sydney
Click to expand...
8 .4
Etc

In another spreadsheet I have 5000+ transactions

Reads like:
A B
Dest kgs
Melbourne 24

Not all the destinations are covered in the named range.
Click to expand...
So I need to Vlookup "Melbourne" and Multiply24 by .5 then
add 10.
Further to that, there is also a minimum of 15 per
Click to expand...
transaction and the problem of the ugly #N/A
 
You're welcome, Alby

sheet2 A$2
is where you have the (1st) look up value of "Melbourne"
Col A, A2 down is where you have the 5000+ lookup values

sheet1 A$1
is the top left corner cell of your reference table
[i.e. where your named range "North" is located]

Since the suggested OFFSET(..MATCH(..)..) can check the
entire column A:A of Sheet1 for matching with the lookup
values in col A of Sheet2, there's no need to reference
the named range "North" in the formula
--

My apologies for the superfluous "Sheet2!"
[in "sheet2!A2"] in the original formula suggested
(really don't know how that crept in there <g>),
since the formula was already in Sheet2 itself.

The "simplified" formula in C2 of Sheet2 should read:

=IF(ISNA(MATCH(TRIM(A2),Sheet1!$A:$A,0)),"",MAX(SUM(OFFSET
(Sheet1!$A$1,MATCH(TRIM(A2),Sheet1!$A:$A,0)-1,2)*B2,OFFSET
(Sheet1!$A$1,MATCH(TRIM(A2),Sheet1!$A:$A,0)-1,1)),15))

But if you're more comfortable with VLOOKUP, here's an
alternative you can put in C2 of Sheet2, which assumes
that your range "North" refers to, say: =Sheet1!$A$1:$C$50

=IF(ISNA(VLOOKUP(TRIM(A2),North,1,0)),"",MAX(SUM(VLOOKUP
(TRIM(A2),North,3,0)*B2,VLOOKUP(TRIM(A2),North,2,0)),15))

Copy C2 down as before
 
Back
Top