Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

User profile for user: limmy3
limmy3 Author
User level: Level 1
8 points

=Lookup for prices

Hello everyone,

here is the one but possibly not the only one with not much experience in making good formulas.

Well, I want to use a table to report my postcards with prices I spent on my holidays.

Fully automatically of course … that's the plan … 😁


User uploaded file



Name

Country

1st

4th

Price Stamps

1

A

Germany

07.06.2018


£ 1.17

2

B

Germany

07.06.2018


£ 1.17

3

C

England

07.06.2018


£ 1.40


LOOKUP(Post cards::D2,Country of destination::$B$2:$D$3,Country of destination::$B$4:$D$4)


This should be the easy formula but even this does not work the way I intend to do.

The table is shortened, I have up to 4 postcards I could send anyone. In the column M the prices for all the stamps paid for a person should appear here. As you can see in the table country of destination the formula shall get the price for the correct World Zone. Thus England should be in Europe for £1.17 and not £1.40.


User uploaded file

COUNTIF(E2:K2,">05.06.2018")×IF(Country of destination::$A$4<TODAY(),LOOKUP(D2,Country of destination::$B$2:$D$3,Country of destination::$B$4:$D$4),LOOKUP(D2,Country of destination::$B$2:$D$3,Country of destination::$B$5:$D$5))


This is the table and formula how it should look like (shortened). As you can see there are two dates 1st 27.3.2017 and 2nd 26.3.2018 which should be considered when the today is after the 2nd date. Well, I know this makes it more complicated but as prices rise every year this would be the easiest way to get the right price. For this I have no solution yet as the first problem is not solved anyway.


Would someone help me, please! It is fun trying to find the solutions but when nothing works it's sad.


Thank you.

Regards limmy3

iMac (27-inch, Late 2013), macOS High Sierra (10.13.2), latest version

Posted on Mar 2, 2018 6:23 AM

Reply
Question marked as Best reply
User profile for user: Barry
Barry
User level: Level 9
59,861 points

Posted on Mar 5, 2018 3:11 AM

Hi limmy,


My interpretation:


You will send up to 4 cards to each person, recording the date of each in columns E,G, I and K, and looking up the price for each in the cell to the immediate right of the cell containing the date, then total those prices in that row of column M.


You want column M to show the total postage cost for each person's cards, and the bottom cell in column M to show the grand total postage cost for all cards.


Here's a rethought set of tables to use with this issue:

User uploaded file

Starting from the "Rates" table at bottom right:


Rates:

This table contains no formulas. It is used to record the current and future postal rates for post cards to destinations in three zones: 0 (the 'local' zone), 1 and 2, and the dates on which each set of rates went/will go into effect.


Destination:

This table contains two columns of entered data: Countries in column A, and their Post zones in column B.

Labels in Row 1 are not used in the VLOOKUP formula I chose, but could be if one chose to use INDEX and MATCH instead.

Cell C2 contains the formula shown below. After entry, it is filled right into D1, then filled down to the last row of the table.


Destination::C2: VLOOKUP($B2,Rates::$A:$C,COLUMN()−1,0)


The formula looks up the postal rate(s) for the zone listed in column B.


Post Cards:


This is a copy of your Post Cards table, with the hidden columns from your example exposed and filled with the data I think they would contain. Note that I have changed the dates from your example to make the 1st and 2nd fall into the period when the first set of rates was in effect, and the 3rd and 4th fall into the period when the second set of rates would apply.


The table contains three formulas,

one entered into F2, filled down, then Copied, and pasted into H2, J2 and L2, filling each of those columns.

the second entered in M2 and filled down that column to the last body row, and

the third, entered into the Footer row cell of column M.


F2: IF(LEN(E2)<1,"",VLOOKUP($D2,Destination::$A:$D,4−IF(E2<Destination::$D$1,1,0)))

English: If there's nothing in E2, place a null string, and exit.

Otherwise, get the Country from D2, look it up in column A of Destination, and return the result from column 4 of the same row (or, IF the date in E2 is before the date in Destination D1, from the column before column 4)


Fill down to the last body row in the column. With all filled cells still selected, Copy.

Click once on H2 to select it. Paste.

Click once on J2 to select it. Paste.

Click once on L2 to select it. Paste.


M2: SUM(F2,H2,J2,L2)


This sums the values in the four cells in the list.

Fill down to the last body row in column M.


M14: SUM(M)


This sums the postage costs in column M.

Row 14 in my table is a footer row. Place the same formula in the Footer row of your table.

You will get an error message if this row has not been defined as a Footer Row.


Regards,

Barry

View in context
9 replies
Question marked as Best reply
User profile for user: Barry
Barry
User level: Level 9
59,861 points

Mar 5, 2018 3:11 AM in response to limmy3

Hi limmy,


My interpretation:


You will send up to 4 cards to each person, recording the date of each in columns E,G, I and K, and looking up the price for each in the cell to the immediate right of the cell containing the date, then total those prices in that row of column M.


You want column M to show the total postage cost for each person's cards, and the bottom cell in column M to show the grand total postage cost for all cards.


Here's a rethought set of tables to use with this issue:

User uploaded file

Starting from the "Rates" table at bottom right:


Rates:

This table contains no formulas. It is used to record the current and future postal rates for post cards to destinations in three zones: 0 (the 'local' zone), 1 and 2, and the dates on which each set of rates went/will go into effect.


Destination:

This table contains two columns of entered data: Countries in column A, and their Post zones in column B.

Labels in Row 1 are not used in the VLOOKUP formula I chose, but could be if one chose to use INDEX and MATCH instead.

Cell C2 contains the formula shown below. After entry, it is filled right into D1, then filled down to the last row of the table.


Destination::C2: VLOOKUP($B2,Rates::$A:$C,COLUMN()−1,0)


The formula looks up the postal rate(s) for the zone listed in column B.


Post Cards:


This is a copy of your Post Cards table, with the hidden columns from your example exposed and filled with the data I think they would contain. Note that I have changed the dates from your example to make the 1st and 2nd fall into the period when the first set of rates was in effect, and the 3rd and 4th fall into the period when the second set of rates would apply.


The table contains three formulas,

one entered into F2, filled down, then Copied, and pasted into H2, J2 and L2, filling each of those columns.

the second entered in M2 and filled down that column to the last body row, and

the third, entered into the Footer row cell of column M.


F2: IF(LEN(E2)<1,"",VLOOKUP($D2,Destination::$A:$D,4−IF(E2<Destination::$D$1,1,0)))

English: If there's nothing in E2, place a null string, and exit.

Otherwise, get the Country from D2, look it up in column A of Destination, and return the result from column 4 of the same row (or, IF the date in E2 is before the date in Destination D1, from the column before column 4)


Fill down to the last body row in the column. With all filled cells still selected, Copy.

Click once on H2 to select it. Paste.

Click once on J2 to select it. Paste.

Click once on L2 to select it. Paste.


M2: SUM(F2,H2,J2,L2)


This sums the values in the four cells in the list.

Fill down to the last body row in column M.


M14: SUM(M)


This sums the postage costs in column M.

Row 14 in my table is a footer row. Place the same formula in the Footer row of your table.

You will get an error message if this row has not been defined as a Footer Row.


Regards,

Barry

Reply
User profile for user: limmy3
limmy3 Author
User level: Level 1
8 points

Mar 3, 2018 6:20 AM in response to Barry

Hello Barry,

thank you again for your great work.

User uploaded file

F2: IF(LEN(E2)<1,"",VLOOKUP($D2,Destination::$A:$D,4−IF(E2<Destination::$D$1,1,0)))


Well, I think I don't need to explain much. From the copy of the formula above it was quite difficult make my formula because just paste it will not work. As you can see I have a string error the blue sign telling the formula compares "a" string with "a" date. Typing in dates before the 27.March.2017 and after the 26.March.2018 will not work. I guess because of the string error.


Thus, where is my mistake?


Destination::C2: VLOOKUP($B2,Rates::$A:$C,COLUMN()−1,0)

VLOOKUP($'World Zone ' Australia,Rates::$A$2:$C$4,COLUMN()−1,0)

User uploaded file

User uploaded file

User uploaded file

User uploaded file

I change the date on the 1st cell and changed the country but it has no effect, none so ever. I think and can imagine

that a formula is incorrect. But which one?


Sorry, Barry to bother you.


Regards limmy3

Reply
User profile for user: SGIII
SGIII
User level: Level 8
35,252 points

Mar 3, 2018 3:12 PM in response to limmy3

Hi limmy3,


Have you tried INDEX MATCH to lookup values? I find that approach is typically much more flexible.


For example you can do something like this:


User uploaded file


The formula in 'Mailings' E2, filled down, is:


=INDEX(Zones::$A:$D,MATCH(D2,Zones::A,0),MATCH(A2,Zones::$1:$1,0))


The formula in C2 of Zones, filled right and down, is:


=INDEX(Rates::$A:$C,MATCH($B2,Rates::$A,0),MATCH(C$1,Rates::$1:$1,0))


User uploaded file


SG

Reply
User profile for user: limmy3
limmy3 Author
User level: Level 1
8 points

Mar 2, 2018 1:31 PM in response to SGIII

SGIII wrote:


However, I'd recommend not using the old LOOKUP function. Instead using its more modern replacements, VLOOKUP and HLOOKUP.


The INDEX MATCH combination is also a good choice.


SG

But none of them did what I intended them to do. With INDEX only the first row is used to select the right request and give the correct column. With the others it’s even worse.

What do I want.

In a table I have three columns for Europe, World Zone 1 and World Zone 2 in which the Countries are listed where they are located. Now the formula finds the country “Germany” in column Europe under which the price £1.17 is listed and gives as result £1.17 as price for a stamp to Europe. And the same with the other counries for the USA £1.40.

Reply
User profile for user: Barry
Barry
User level: Level 9
59,861 points

Mar 5, 2018 2:58 AM in response to limmy3

Hi limmy,


The 'string error' results from comparing a date and time value in one of the cells and a text value ('string') in the other.


The two cells are Destination::D1 and E2 in the table containing the formula.

Click on each of these cells in turn, and look at the box at the bottom left of the document window. For one of the cells, it will show a full date and time value (eg. 23.03.2018 00:00:00 ) for one of the cells, and a text value ( eg. 28.3.2018 ) for the other. For the comparison to work, both cells must contain Date & Time values.


One thing that may be throwing Numbers off task here is the variation in the 'date' annotations you've used, specifically the inclusion or exclusion of a leading zero in the numerical representation of March ( some are "3" others are "03").


Regards,

Barry

Reply
User profile for user: limmy3
limmy3 Author
User level: Level 1
8 points

Mar 5, 2018 3:11 AM in response to Barry

Hi Barry,

thank you very much for your help.

Correcting the date string solved the error.

I used the first example but thank you for the alternative.

I collect the example given from other members to learn.

Every time I think the answer could be as easy as I "always" set it up. Just use =Lookup and that's it.

Every time I am astonished how complex these things are.


Well, that is the difference between professionals and someone like me. Amateurs!


Have a nice day!

Regards limmy3

Reply
User profile for user: Barry
Barry
User level: Level 9
59,861 points

Mar 5, 2018 4:30 PM in response to limmy3

"Well, that is the difference between professionals and someone like me. Amateurs!"


Much of the time, the only difference between 'amateurs' and 'professionals' is a matter of time and experience. Experience, and learning from experience, is the key, one you appear to be taking an interest in…

"I collect the example given from other members to learn."


Keep doing that! Before long, you'll be answering questions here (as well as asking questions).


Regards,

Barry

Reply

=Lookup for prices

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple ID.
Learn more Sign up