IF & Lookup Functions

[Emma]

I'm working on a spreadsheet with much financial data and
trying to build a formula that will look to a cell in a
prior column, where there may or may not be a value, and
if there is a target value there, will look to another
column for the definition, then use hlookup (my
definitions are column headers with the financial data in
rows below) to compare the actual value to the target.
These will rarely be exact, so I'd like to return a
response of "OK" if within 5% or "No" if more of a
variance. At this point I have not even attempted the 5%
either way, but am testing my formula on an exact match
and I'm getting a response of "No". I've been reading a
lot of these messages and clearly you all know how to go
about this! Here's what I've got so far: =IF(AND
(AB4>=HLOOKUP(AA4,$U$1:$Z$800,4,FALSE),AB4<=HLOOKUP
(AA4,$U$1:$Z$800,4,FALSE)),"ok","no") Any suggestions
will be greatly appreciated.

 

[Frank Kabel]

I'm working on a spreadsheet with much financial data and
trying to build a formula that will look to a cell in a
prior column, where there may or may not be a value, and
if there is a target value there, will look to another
column for the definition, then use hlookup (my
definitions are column headers with the financial data in
rows below) to compare the actual value to the target.
These will rarely be exact, so I'd like to return a
response of "OK" if within 5% or "No" if more of a
variance. At this point I have not even attempted the 5%
either way, but am testing my formula on an exact match
and I'm getting a response of "No". I've been reading a
lot of these messages and clearly you all know how to go
about this! Here's what I've got so far: =IF(AND
(AB4>=HLOOKUP(AA4,$U$1:$Z$800,4,FALSE),AB4<=HLOOKUP
(AA4,$U$1:$Z$800,4,FALSE)),"ok","no") Any suggestions
will be greatly appreciated.

Hi Emma
try the following:
IF(AA4<>"",IF(AND(AB4<=HLOOKUP(AA4,$U$1:$Z$800,4,FALSE)+0.05,AB4>=HLOOK
UP(AA4,$U$1:$Z$800,4,FALSE)-0.05),"OK",NO"),"")
where AA4 is your value in the 'prior' column used to lookup the tagert
value in row 4 of your range U1:Z800 (you can change this to U1:Z4) and
compare this to the value in AB4.


HTH
Frank

 

[Emma]

-----Original Message-----


Hi Emma
try the following:
IF(AA4<>"",IF(AND(AB4<=HLOOKUP(AA4,$U$1:$Z$800,4,FALSE) +0.05,AB4>=HLOOK
UP(AA4,$U$1:$Z$800,4,FALSE)-0.05),"OK",NO"),"")
where AA4 is your value in the 'prior' column used to lookup the tagert
value in row 4 of your range U1:Z800 (you can change this to U1:Z4) and
compare this to the value in AB4.


HTH
Frank

.

Hi Frank,
Thanks so much for your help - yes, that works
beautifully! One further question: In the phrase
(AA4,U1:Z4,4,FALSE) 4 is, of course, the row the data is
on. I'd like to copy this formula down the 800 rows, but
the 4 remains the same. Any way to have it reflect the
row number it's copied to, or is this just a good lesson
in how to structure future worksheets?

Emma

 

[Frank Kabel]

Hi Frank,
Thanks so much for your help - yes, that works
beautifully! One further question: In the phrase
(AA4,U1:Z4,4,FALSE) 4 is, of course, the row the data is
on. I'd like to copy this formula down the 800 rows, but
the 4 remains the same. Any way to have it reflect the
row number it's copied to, or is this just a good lesson
in how to structure future worksheets?

Emma

Hi Emma
if I understood you correctly and you put the formula also in row 4 try
the following:
IF(AA4<>"",IF(AND(AB4<=HLOOKUP(AA4,$U$1:$Z$800,ROW(),FALSE)+0.05,AB4>=H
LOOK UP(AA4,$U$1:$Z$800,ROW(),FALSE)-0.05),"OK",NO"),"")
now copy down

Frank

 

[Guest]

-----Original Message-----


Hi Emma
if I understood you correctly and you put the formula also in row 4 try
the following:
IF(AA4<>"",IF(AND(AB4<=HLOOKUP(AA4,$U$1:$Z$800,ROW (),FALSE)+0.05,AB4>=H
LOOK UP(AA4,$U$1:$Z$800,ROW(),FALSE)-0.05),"OK",NO"),"")
now copy down

Frank

.

Hi Frank,
That did it! Many, many thanks for your help!
Emma