+ Reply to Thread
Results 1 to 20 of 20

IF column F equals column H equals

  1. #1
    Registered User
    Join Date
    06-17-2020
    Location
    London, England
    MS-Off Ver
    Pro 2016
    Posts
    55

    IF column F equals column H equals

    Hi, I've advanced a lot with my excel considering it was zero a few months ago. I always get a bit confused with IF function often going to Youtube for help.

    The items in Column F are stock and their price is in Column H. I want Column H to always return it's price if the user inputs data in Column F.

    For example if I type Pencils in Column F I want Column H to return 0.11.

    See attachment for reference.

    Thanks Andy
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    06-17-2020
    Location
    London, England
    MS-Off Ver
    Pro 2016
    Posts
    55

    Re: IF column F equals column H equals

    Would it be easier if I put the price in Column F alongside the product. For example "Balloons £0.13" then every month use delimiter to separate and give the data to the person who needs it. I'm likely to be the only person using this spreadsheet.

  3. #3
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,404

    Re: IF column F equals column H equals

    I'm not clear what it is you want. You already have Pencils in F11 with £0.11 in H11.
    Do you want to be able to type (or select) 'Pencils' in another cell and have the price per unit appear next to it?
    Or is it that as a new entry is put in col F (F27 onwards), a price appears automatically in H? If so, where is this price coming from?
    Or, third option, do you want users to be able to type from F27 onwards and if they enter something which already exists above, then the price will be automatically filled in?

    All of these will need a lookup of some sort, not an IF. Once we know what it is you need, we can suggest how to do it. I'm just a bit reluctant to start suggesting methods until then, as I'll probably just confuse matters.
    Regards,
    Aardigspook

    I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.

  4. #4
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: IF column F equals column H equals

    I think you should built a data set of products with their prices, and do a vlookup in your stock database.

  5. #5
    Registered User
    Join Date
    06-17-2020
    Location
    London, England
    MS-Off Ver
    Pro 2016
    Posts
    55

    Re: IF column F equals column H equals

    What's a lookup?

    It's the third option I'm looking to achieve.

    Cheers

  6. #6
    Registered User
    Join Date
    06-17-2020
    Location
    London, England
    MS-Off Ver
    Pro 2016
    Posts
    55

    Re: IF column F equals column H equals

    Just seen vlookup on youtube. That's exactly what I'm after. Not sure sunday evening was a great time to start this. lol!

  7. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: IF column F equals column H equals

    It would be handy if you set up a table (perhaps in another sheet) which has a unique list of items in column A, along with their price in column B.

    Then you can use this formula in cell H2 of Sheet1:

    =IF(F2="","",IFERROR(VLOOKUP(F2,Sheet2!A:B,2,0),"unknown item - add to table"))

    Copy this down as far as you think you may need, then as you add a new item to the Product column the price will automatically be displayed. If the item is not found in the table on Sheet 2 then you will get an error message informing you to add the details to the table.

    Hope this helps.

    Pete

  8. #8
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,404

    Re: IF column F equals column H equals

    A lookup is when you refer to one entry in a table and look up a corresponding bit of data elsewhere, hence the name.

    In this case, belinda200 is correct that you'd be best to have a separate table with all your stock/prices in it.

    However, to give you an idea of how the lookup could work, here are two ways to do it using just the table you have.
    These formulae would go in H27, to return a price for whatever has been entered in F27. I've added an error check for when the typed entry doesn't exist. In that case, you'd have to check the spelling and/or enter a new price if the entry doesn't already exist.
    In both cases, enter the formula in H27 then drag down as far as you want.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    If those give you the sort of results you'd like, we can explain further how they work.

  9. #9
    Registered User
    Join Date
    06-17-2020
    Location
    London, England
    MS-Off Ver
    Pro 2016
    Posts
    55

    Re: IF column F equals column H equals

    Thanks Pete, bit above my station that!

    I think understand what it's going to. I'll put my products and prices on one sheet and the formula will reference it? Do you know of a youtube link as I want to gain better understanding rather than copy and paste.

    You've got almost 20000 posts! That's dedication!

  10. #10
    Registered User
    Join Date
    06-17-2020
    Location
    London, England
    MS-Off Ver
    Pro 2016
    Posts
    55

    Re: IF column F equals column H equals

    Thanks Aardigspook I'll look tomorrow AM. Very quick responses

  11. #11
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: IF column F equals column H equals

    The data that you have in your example file is already a unique list (and it is sorted, though it doesn't need to be for this). So, you can just copy from columns F, G and H and paste into sheet2 at A1, and then you can delete column B in that sheet and you will have your table already set up.

    Then you can use the formula that I gave you in cell H2 of the first sheet and copy it down.

    I don't look at You Tube videos, so I can't help you there.

    Hope this helps.

    Pete

  12. #12
    Registered User
    Join Date
    06-17-2020
    Location
    London, England
    MS-Off Ver
    Pro 2016
    Posts
    55

    Re: IF column F equals column H equals

    Thanks Pete I’ll give it a go and study the formula.

    I imagine you don’t need the help of YouTube as you are the help! Lol!

  13. #13
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: IF column F equals column H equals

    That's right.

    If you find that the approach takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

    Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile). You should do this for ALL helpers.

    Pete

  14. #14
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,404

    Re: IF column F equals column H equals

    I don't use YouTube either but there are plenty of tutorials on the net. There have been literally thousands of threads about various types of lookups on this forum alone.
    Also don't be afraid to look at the help function in Excel - the explanations in there are actually quite good.
    MS also have guides to all functions online - here's the link to an introduction to VLookup / Index-Match: https://support.microsoft.com/en-gb/...c-4d348188976b

  15. #15
    Registered User
    Join Date
    06-17-2020
    Location
    London, England
    MS-Off Ver
    Pro 2016
    Posts
    55

    Re: IF column F equals column H equals

    Thanks all, the formula posted above didn’t work. Although I did hack it together during a quick break from work. I’ll post it up here to tomorrow.

    Yes I’ll have to learn on YouTube it’s finding the right presenter. Some are very clear other get stuck explaining sub issues.

  16. #16
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,404

    Re: IF column F equals column H equals

    Here's a file using Pete_UK's suggestion from post 7 - your existing list is on the sheet 'Products' with his formula in H2 and dragged down (I've shaded the cells to show how far).

  17. #17
    Registered User
    Join Date
    06-17-2020
    Location
    London, England
    MS-Off Ver
    Pro 2016
    Posts
    55

    Re: IF column F equals column H equals

    Thanks very much Aardigspook. I'll have a look tonight after work.

  18. #18
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,404

    Re: IF column F equals column H equals

    Thanks for the rep. If that means that you're happy that your question has been answered, then please take a moment to mark the thread as Solved so others know there's an answer here (instructions are in my sig). Thanks.

  19. #19
    Registered User
    Join Date
    06-17-2020
    Location
    London, England
    MS-Off Ver
    Pro 2016
    Posts
    55

    Re: IF column F equals column H equals

    Hi Aardigspook, it didn't really solve my issue as I've changed how the document works but was much appreciated as you sometimes need to know good and bad practice. Thanks

  20. #20
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,404

    Re: IF column F equals column H equals

    You're welcome, glad we could be of some assistance.

    If there's anything else we can help with, just ask - in another thread though, if it's not related to this question.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 02-23-2018, 07:04 PM
  2. [SOLVED] Count if month column AND numerical column equals
    By Skiczn in forum Excel General
    Replies: 4
    Last Post: 12-06-2016, 01:21 AM
  3. Replies: 7
    Last Post: 04-01-2016, 03:27 PM
  4. Replies: 7
    Last Post: 11-13-2014, 04:46 PM
  5. [SOLVED] Need to count if Column A contains definded text and Column B equals defined word
    By JBlo in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-26-2014, 01:38 PM
  6. Replies: 4
    Last Post: 09-06-2012, 09:26 AM
  7. if column B equals "cash" then column H equals 0
    By im3d in forum Excel General
    Replies: 4
    Last Post: 04-26-2011, 04:43 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1