Leave values "as is" in a IF statement/Hlookup

[robread]

I am trying to develop a formula that uses an IF statement and an H lookup. It will look to see what the date is and if it matches will import that cell.

The problem is that I only have 1 column that it is looking up and this column will continually change as the date changes. So I want the formula to "paste special" so the values come in and replace the formula.

In otherwords if the value of the statement is false, then it will leave the values as they were.

Is this possible?

 

[Andrew Poulsom]

How can you use HLOOKUP on only one column?

Post an example of your data and the expected results.

 

[robread]

=IF(HLOOKUP($C$9,'Input Sheet'!G9:G65,4,FALSE) - So this looks up the column to ensure that the dates (ie C9) match. If it does it will return the value.

I dont know how to attach a spreadsheet here - hope this helps.

 

[Andrew Poulsom]

Should it be VLOOKUP? Your data is in a column (G).

 

[robread]

As far as I know, the Vlookup will attempt to locate cells that match a value in a defined range.

I want the formula to say if the dates match then give me row 4 in column G.

Then I want it to say - OK they dont match anymore (as the dates have now changed)- but keep the values in the cell as they were.

Is this possible?

 

[Andrew Poulsom]

Where is the date you are trying to match with C9?

 

[robread]

The date will match with a cell in another worksheet. Say c9 'sheet 1' and G9 'sheet 2'.

Somebody suggested If(isna...) I have no idea what isna is!

I think that it will require a macro that says if formula is false, then paste special.

Cheers

 

[Andrew Poulsom]

Don't you just need this formula:

=IF(C9='Input Sheet'!G9,'Input Sheet'!G12,0)

where G12 is the fourth cell in the range G9:G65 on sheet "Input Sheet"?