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

Solved

Posted on 2011-10-06

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.

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.

11 Comments

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

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(H

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

By clicking you are agreeing to Experts Exchange's Terms of Use.

Title | # Comments | Views | Activity |
---|---|---|---|

Automate double vlookup using VBA | 11 | 56 | |

Counting # of WOs in Excel meeting specific criteria (updated) | 3 | 16 | |

Excel spreadsheet only viewable in full screen mode | 5 | 34 | |

excel - employee availability remaining after scheduled | 7 | 34 |

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

Connect with top rated Experts

**18** Experts available now in Live!