Formula Help Needed

[TheDave]

I've got a complex formula I need some help writing. Here's a simplified example of what I'm dealing with:

Cell G2 has a value that might appear in range G10:G400. If it does appear there, I want the information in the neighboring cell. So if the value of G2 is also found in G203, then I want the information in H203. Of course, I'd be putting this formula in cell K2, so that's where the information found in H203 would be duplicated. If the value of cell G2 does not appear in G10:G400, I just want a zero returned in K2.

Help?

 

[VoG]

Try

=IF(ISNUMBER(MATCH(G2,G10:G400,0)),INDEX(H10:H400,MATCH(G2,G10:G400,0)),0)

 

[Expiry]

Try this

=IF(COUNTIF(G10:G400,G2),VLOOKUP(G2,G10:K400,5,0),0)

 

[TheDave]

Thanks, VoG, worked great!