OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

IF function or Lookup????? Help!!

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
davey0710
General User
General User


Joined: 10 Mar 2011
Posts: 11

PostPosted: Thu Mar 10, 2011 1:30 pm    Post subject: IF function or Lookup????? Help!! Reply with quote

I am trying to do a cost sheet for a sandwich counter using Calc
I have a line for each filling, (about 30rows) although the cost changes dependant on the bread type used.
I have put the bread types with costs in a separate sheet.
the column next to where I want the result to appear I have used the Data Validation tool so I select the bread type from a list of preset values.
I will use the result to add to the other set costs in the process.
I initially used the IF function, although after reading about Lookup/Vlookup and Hlookup I think this may be more suitable, due to when you auto fill the formula in the next cells it also skips the cells I am extracting from down one in the formula aswell.

Could someone please give me some advice as to how to progress please, I have a standard knowledge of spreadsheets, so if the answer is quite complex I may need a bit of english explanation.

Thankyou in advance
Back to top
View user's profile Send private message
keme
Moderator
Moderator


Joined: 30 Aug 2004
Posts: 2910
Location: Egersund, Norway

PostPosted: Thu Mar 10, 2011 2:44 pm    Post subject: Reply with quote

Little detail here, so I just make one guess:You only want exact matches.
If so, make sure you use the optional fourth parameter to VLOOKUP()/HLOOKUP(), set to the value 0 (zero) or its equivalent logical value, FALSE.

Also, note that VLOOKUP/HLOOKUP makes a single (one dimensional) lookup using the first field of the data range (leftmost column/topmost row). If that doesn't fit your data, you need a MATCH()/INDEX() set. The LOOKUP() function, while offering some additional flexibility, does not have that optional "sorted" parameter which is used to restrict returns to exact matches. Instead it always assumes a sorted lookup vector, and returns the nearest match when no exact match is found.
Back to top
View user's profile Send private message
davey0710
General User
General User


Joined: 10 Mar 2011
Posts: 11

PostPosted: Thu Mar 10, 2011 4:19 pm    Post subject: Reply with quote

apologies keme for lack of detail.

i will explain fully the end result at present i would like from this data, it has altered slightly although my question is still almost the same, it is still the same result i am trying to achieve.

I am using this as a starting point as once this has been cracked i am quite sure I can get the other elements i need in the same/similar way.

Sheet1 -
Column A has a list of sandwich fillings
Column B has a list of costs relating to the filling next to it

On sheet 2
Column 1 - I would like to have a dropdown box in a cell, from the range of Column A on sheet 1.
Once the description is selected from the drop done menu, I would then like the corresponding value from Column B on sheet1 to be shown next to the selection i have made on sheet2.

I no how to do this using the IF function although the amount of data in the range means i will be there for days using that particular function, I just thought there must be another way.

Does this make more sense or am i still being confusing??

I have tried using Match or index and cant seem to get it right.
Back to top
View user's profile Send private message
davey0710
General User
General User


Joined: 10 Mar 2011
Posts: 11

PostPosted: Fri Mar 11, 2011 1:37 am    Post subject: Reply with quote

Is anyone able to help???
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group