Find a Value in Row and Reference a Cell in Same Column as Value

**TylerJamison** · 08-13-2012, 06:18 PM

Hi there,

What I'd like to do is find the value "H" in a series of cells (let's say F3:AC3, for example) and return the value (which is a date) from row 2 of that same column. For example, if there is an "H" (a code indicating that the crop has been harvested) in cell H3, I would like Excel to return the value that is in H2. This is easy to do once, but I need it to work automatically for hundreds of different crops.

Any ideas?

Thank you.

**xladept** · 08-13-2012, 06:32 PM

Hi TylerJamison,

You could try this - if you have a destination in mind, we could write a subroutine - it's not tested:

Please Login or Register  to view this content.

**TylerJamison** · 08-14-2012, 10:25 AM

Where do I place this code in the spreadsheet?

**TylerJamison** · 08-14-2012, 11:17 AM

I figured it out. I ended up writing this formula:

=IF(ISERROR(INDEX($F$2:$AC$2,MATCH("H",$F3:$AC3,0))),"-",INDEX($F$2:$AC$2,MATCH("H",$F3:$AC3,0)))

Works perfectly.

Thanks for your help.

**xladept** · 08-14-2012, 11:44 AM

Yeah - I'm a VBA guy - you could use the function, I wrote (with a little editing) as you've used the Worksheet functions. The code would go in a Standard Module. Good that you figured it out.