Matched 2 sets of coluumns then multiply 2 others

[skell1]

All data is contained on one sheet. Here is what I am looking for.

I need Column A to look for and match N2 or R2 or Z2

I then need to match column E to column M

I then need to multiply column d to the cell in N R or Z from step 1.

I appreciate any help.

[skell1]

This is what I am trying, but it fails

=IF(AND (A2:A415=N2 or R2 or Z2 IF(E2:E301=M3:M38) D2:D301*N3:N38 or R3:R38 or Z3:Z38))

[Glenn Kennedy]

Hi there... and welcome to the Excel Forum. Please attach a sample workbook. Make sure there is enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove ALL confidential information first!!!

Click on GO ADVANCED and use the paperclip icon to open the upload window.

The paperclip icon

[skell1]

I have attached a snippet of my sheet.

What I need is E to match the corresponding team in I, then A needs to match J, N, V or Z. Then D needs to add or subtract the corresponding percentage from D.

I want to run this in column G.

So for row one, the calculation would be D2 calculated against J33.

I know this is a bit convoluted. Again, thanks in advance for the help.

[FlameRetired]

This formula in column G will take you this far.

What I need is E to match the corresponding team in I, then A needs to match J, N, V or Z.

after that I do not understand your meaning

Then D needs to add or subtract the corresponding percentage from D. .....
So for row one, the calculation would be D2 calculated against J33.

Please Login or Register  to view this content.

Also check the headers in row 1 or your data. At least one of them has trailing spaces.

[skell1]

A huge thanks upfront! Column D is the projection. Column J represents the percentage to be added or subtracted from the projection in D.

[FlameRetired]

If I understand correctly change the above formula to

Please Login or Register  to view this content.

Are we getting close?

[skell1]

Very close. Formula is not seeing WR2 which is N. Not sure why. Also, D is my projection, so when I get a match in J, N V or Z, I need to add or subtract that percentage from the corresponding number in D.

So the final calculation for row 1 would be 26.525 + 48.5% Negative percentages would be D - the corresponding percentage.

I cleaned up the spaces.

[FlameRetired]

Try this formula.

Please Login or Register  to view this content.

It returns 39.389625 for 26.525 in row 1. Since WR1 / SF intersect at 0.485 a 48.5% increase should be 39.389625. Likewise at row 5 column D is 19.55. WR1 / OAK intersect at -0.127 .... a 12.7% decrease and that should result in 17.06715 .... which it does.

so when I get a match in J, N V or Z, I need to add or subtract that percentage from the corresponding number in D

I took the signs of those numbers to tell us whether to add or subtract. Have I been reading the data table wrong? Are we to understand that positive numbers are decreases and negatives increases?

Formula is not seeing WR2 which is N. Not sure why.

Check the data in column A as well for leading / trailing / internal spaces. Since I don't have any WR2s in column A of the upload I cannot say. However when I change some of the items in column A to WR2 the formula works at my end.

[skell1]

The data from columns J, N, V & Z represent the percentages above and below average for yards. So a negative indicates they give up that percentage fewer yards, positive is percentage above average for yards.

Again, I can't tell you how much I appreciate your help.

I had spaces, WR2 works as expected after I cleaned it up.

[skell1]

And I just tested, it works perfectly!! Thank you very much!

[FlameRetired]

Glad to here it, and you're welcome.