If then help!

jtramell

New Member
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

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Welcome to MrExcel board...

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

where do you want to paste the copied data?

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

I want to copy the data to a A32

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

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

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

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

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

Replies
3
Views
956
Replies
3
Views
162
Replies
6
Views
657
Replies
1
Views
317
Replies
4
Views
623

1,217,332
Messages
6,135,941
Members
449,974
Latest member
riffburn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back