Test IF Two Numeric Values Match (from two separate Dynamic Ranges)

[Sam via OfficeKB.com]

Hi All,

I have two Dynamic Ranges: "ACTUAL" spans 7 Columns and many Rows. "FORECAST"
is one Column and many Rows.

I would like to check if the numeric values in the LAST Column of "ACTUAL"
match the values in "FORECAST" on the same Row. If there is a match on the
same Row, I would like a text string of "yes" returned; blank if no match.

The numeric values in "ACTUAL" are all integers. However, the numeric values
in "FORECAST" are a mix of integers and numbers to five decimal places.

So, if the LAST Column of "ACTUAL" has a value of 61 and the corresponding
Row in "FORECAST" has a value of 61.33333 then, this should be taken as a
match. However, if the value in "FORECAST" is 61.66667 then, this is viewed
as 62 and is NOT a match with 61.

Thanks
Sam

 

[Biff]

Hi!

From your description it sounds like only the number of rows in your ranges
are dynamic.

Just because the ranges are named doesn't mean you have to use those names
in any formulas.

Assume that column 7 of ACTUAL is in column G, G2:Gn

FORECAST is in column H, H2:Hn

=IF(OR(G2="",H2=""),"",IF(ROUND(H2,0)=G2,"Yes",""))

Copy down to enough rows to allow for future addition of data in the named
ranges.

Now, here's that same formula using the range names:

=IF(OR(INDEX(Actual,ROW(1:1),7)="",INDEX(Forecast,ROW(1:1))=""),"",IF(ROUND(INDEX(Forecast,ROW(1:1)),0)=INDEX(Actual,ROW(1:1),7),"Yes",""))

Biff

 

[Sam via OfficeKB.com]

Hi Biff,

Thank you very much for both alternatives - great! Very much appreciated.

Assume that column 7 of ACTUAL is in column G, G2:Gn
FORECAST is in column H, H2:Hn
=IF(OR(G2="",H2=""),"",IF(ROUND(H2,0)=G2,"Yes",""))

Copy down to enough rows to allow for future addition of data in the named ranges.

Now, here's that same formula using the range names:
=IF(OR(INDEX(Actual,ROW(1:1),7)="",INDEX(Forecast,ROW(1:1))=""),"",IF(ROUND(INDEX(Forecast,ROW(1:1)),0)=INDEX(Actual,ROW(1:1),7),"Yes",""))

Cheers,
Sam

 

[Biff]

You're welcome. Thanks for the feedback!

Biff

Hi Biff,

Thank you very much for both alternatives - great! Very much appreciated.


Cheers,
Sam