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

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

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
How can you use HLOOKUP on only one column?

Post an example of your data and the expected results.

=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.

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

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?

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

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

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"?

Replies
2
Views
332
Replies
0
Views
223
Replies
4
Views
263
Replies
3
Views
204
Replies
4
Views
493

1,203,187
Messages
6,053,995
Members
444,696
Latest member
VASUCH

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.
Allow Ads at MrExcel

Which adblocker are you using?

Follow these easy steps to disable AdBlock

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

Follow these easy steps to disable AdBlock Plus

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