Nested IFs:bottom row of dates

**garethgtt** · 01-21-2007, 10:50 AM

Hi,

I have a row of Dates with figures under each date and further down another row of dates.. i need to some kind of formula that can be run from the second row of dates to check if the 2 rows of dates are the same and if it is to copy the numbers from the first row underneath the second.
i am doing this with nested IF statments at the moment, however my first row now has more than 7 dates which means the way i am doing it wont work anymore.
Either row of dates could start from different dates at any point in the future
I also need to be able to copy and paste the formula across the bottom row of dates and have it adjust itself relative to the cell its being copied into

I have included an example sheet to illustrate my point.

Any help would be greatly appreciated!

**VBA Noob** · 01-21-2007, 11:50 AM

Hi,

Does this help

=IF(ISNA(HLOOKUP(B15,$B$5:$M$6,2,FALSE)),0,HLOOKUP(B15,$B$5:$M$6,2,FALSE))

VBA Noob

**garethgtt** · 01-21-2007, 12:49 PM

Hi, Thanks that works fine.

I also at the same time found that:
=LOOKUP(B19,$B$5:$M$5,$B$6:$M$6)
works exactly the same.

could you elaborate on any differences between the 2?
ah yours shows zero if there is no date weras mine shows N/A

Thanks!

**VBA Noob** · 01-21-2007, 01:39 PM

Hi,

HLookup is for Horizontal lookup. If you had to return the value in row 2 or 3 etc then you would use this.

If it can't find your value then it returns #N/A. To avoid this use a if statement. Can return whatever you want In the below example (See bold) it returns 0. If you want it blank then replace zero to "".

e.g

=IF(ISNA(HLOOKUP(B15,$B$5:$M$6,2,FALSE)),0,HLOOKUP (B15,$B$5:$M$6,2,FALSE))

See link for another example

http://www.j-walk.com/ss/excel/usertips/tip010.htm

VBA Noob

Nested IFs:bottom row of dates

LinkBack

Thread Tools

Rate This Thread

Display

Nested IFs:bottom row of dates

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions