Solved

# 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
255 Views
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.
0
Question by:JaseSt

LVL 92

Expert Comment

You can do this with a formula in Col I:

=IF(H2=C2,B2,"")
0

Author Comment

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

Author Comment

because inserting that formula in Col i did nothing
0

LVL 5

Expert Comment

You may use this formula and apply it for the whole Col. I:
``````=IF(H1=C1,B1,"")
``````
0

Author Comment

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

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?
0

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.

McOz

0

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
0

LVL 3

Expert Comment

I reccon you need a vlookup.
Type this into cell I1:
=if(iserror(vlookup(h1,C:C,1,false)),"",B1)
0

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

=INDEX(B:B,MATCH(H2,C:C,0))

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
27383358.xlsx
0

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.
0

## Featured Post

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.