If then help!

[jtramell]

I am trying to write a formula that will look at H14:H20 & R14:R20 to look for an "X", if there is an X in one of those boxes I want it to copy the number in E14:E20 & O14:O20 in the corresponding row. I had it once but think that I stumbled on it by accident and when IT upgraded the software to 2003 from 2000 it got "lost in translation" and now that is the only formula that does not work. Please help,

Thanks
John

 

[texasalynn]

Welcome to MrExcel board...

so what is the formula you have that does not work?

 

[jbvinny]

where do you want to paste the copied data?

 

[jtramell]

Its gone and cant find one in the editor that will work for me. I am trying to use =IF(H14:H20,R14:R20="X",E14:E20,O14:O20,0) It is not working though and I can not figure out what the formula was before that worked and the backup was corrupted in the excel update. Thanks

 

[jtramell]

I want to copy the data to a A32

 

[Weaver]

try editing the formula and then instead of typing enter key to exit, thype enter whilst holding ctrl+shift

 

[texasalynn]

maybe this?
=INDEX(E14:E20,MATCH("X",H14:H20,0))

 

[jtramell]

It's telling me too many arguments. I have tried nesting it with this formula but it is very long.

=IF(H14="X",E14,IF(H15="X",E15,IF(H16="X",E16,IF( ))))so and and so on.

would VLOOKUP be another possible way to go? I have never used this before.

Thanks

 

[jtramell]

I think I fixed it with a work around. I placed the following formulas to accomplish this.

=sumproduct((E14:E20)*(H14:H20="X")) in one hidden cell
=sumproduct((O14:O20)*(R14:R20="X")) in another hidden cell
then I just auto sum those two hidden cells in A32 for my information

Its a long way to go for such simple information. Thanks for your assitance though with this matter.

John

 

[jbvinny]

I think =VLOOKUP() would have a problem with your table having columns in between that are not part of your table.