Excel 2007: If a value in col H = a value Col C put the value of Col B in Col i next to its matching value in Col H

Posted on 2011-10-06
Last Modified: 2012-05-12
simple question and need a formula to work like this:

If a value in col H = a value Col C put the value of Col B in Col i next to its matching value in Col H

Can give me a button to click if want or an easy way to implement in my spreadsheet.

Thank you.
Question by:JaseSt
    LVL 92

    Expert Comment

    by:Patrick Matthews
    You can do this with a formula in Col I:


    Author Comment

    Thank you, but how do I implement it? Put it in the top row of Col i and copy down?

    Author Comment

    because inserting that formula in Col i did nothing
    LVL 5

    Expert Comment

    You may use this formula and apply it for the whole Col. I:

    Open in new window


    Author Comment

    how do I apply it? inserting the code produces a blank cell in col i
    LVL 5

    Expert Comment

    It should produce a blank cell if H and C are different.
    Or else what do u want it to display?
    LVL 9

    Expert Comment

    "how do I apply it? inserting the code produces a blank cell in col i"

    Insert the formula in column I, but then make sure that there is a value in cell B1, and that H1 equals C1. If these conditions are not met, the formula would still be working correctly by returning a blank.



    Author Comment

    the value in row h2 is not the same as the value in row c2. the value could be anywhere in col C
    LVL 3

    Expert Comment

    I reccon you need a vlookup.
    Type this into cell I1:
    LVL 50

    Accepted Solution

    Hello JaseSt

    I'm assuming you want it to work like this:

    Let's assume that H2 matches with C13 then you want to put the value from B13 in I2? If so then you can't use VLOOKUP in it's usual form because you want to retrieve a value from a range to the left of the lookup range, so use INDEX and MATCH, i.e. in I2 copied down


    If H2 doesn't find a match in column C then that will give you an #N/A error. You can add IFERROR function to avoid that, i.e.

    =IFERROR(INDEX(B:B,MATCH(H2,C:C,0)),"No Match")

    Replace "No match" with any text you want or leave the quotes empty ("") for a blank

    see attached example

    Note: if you have a large amount of data in column H then you can easily "fill down" the formula like this:

    Paste formula into I2 and select that cell

    Put cursor on bottom right corner of the cell until you see a black "+" - this is the "fill-handle" - double-click and the formula will automatically fill all the way down as far as you have data in an adjacent column.

    regards, barry

    Author Closing Comment

    Wow! That is a beautiful thing. Thank you barryhoudini. And, thank you to all the rest who answered as well, but barryhoudini gave exactly what I needed and explained it well enough for a novice like me to understand. I will use this formula again and again, I know.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
    Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now