need formula instead of macro

[Savuti]

Hello, I need a formula in "C9" that will search cells "C3:H3" to match a date in "A5" then copy the corresponding data in row "C6:H6" to "C9".

Is it possible to have a formula do this instead of a macro.

Thanks for the help.

 

[Marcelo Branco]

Hi,

Maybe this.

Formula in C9
=IF(ISNUMBER(MATCH($A$5,$C$3:$H$3,0)),C6,"")
copy across til H9

HTH

M.

 

[rudfaden]

If you just want to copy the cells as they appear in row 3 use

=IF($A$5=C3;C3;"")

If you want to actually search the whole array use

=HLOOKUP($A$5;$C$3:$H$3;1;FALSE)

 

[Savuti]

Hello Marcelo, I have entered the formula you gave. It so happens that the date in "A5" matches to the date in "E3" so it should pull the data from "E6" to "C9" - the formula copies data from "C6" to "C9"

 

[Marcelo Branco]

Sorry, i misunderstood your problem. I thought you need to copy the entire row 6 to row 9.

Try this in C9
=IF(ISNUMBER(MATCH($A$5,$C$3:$H$3,0)),INDEX($C$6:$H$6,MATCH($A$5,$C$3:$H$3,0)),"")

HTH

M.

 

[Savuti]

Marcelo thank you very much, formula works great.

 

[Marcelo Branco]

Or if you have Excel 2007 or higher

=IFERROR(INDEX($C$6:$H$6,MATCH($A$5,$C$3:$H$3,0)),"")

M.

 

[Marcelo Branco]

Marcelo thank you very much, formula works great.

Tks for the feedback

You are welcome!

M.