Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 304
  • Last Modified:

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

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
JaseSt
Asked:
JaseSt
1 Solution
 
Patrick MatthewsCommented:
You can do this with a formula in Col I:

=IF(H2=C2,B2,"")
0
 
JaseStAuthor Commented:
Thank you, but how do I implement it? Put it in the top row of Col i and copy down?
0
 
JaseStAuthor Commented:
because inserting that formula in Col i did nothing
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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

Open in new window

0
 
JaseStAuthor Commented:
how do I apply it? inserting the code produces a blank cell in col i
0
 
bitrefCommented:
It should produce a blank cell if H and C are different.
Or else what do u want it to display?
0
 
McOzCommented:
"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
 
JaseStAuthor Commented:
the value in row h2 is not the same as the value in row c2. the value could be anywhere in col C
0
 
Davy2270Commented:
I reccon you need a vlookup.
Type this into cell I1:
=if(iserror(vlookup(h1,C:C,1,false)),"",B1)
0
 
barry houdiniCommented:
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
 
JaseStAuthor Commented:
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now