Help with Formula

[Frick]

In H4:AA4 I have Dates = to start of the month

In B18:B37 I have dates that are not consecutive. Such as 1/1/06, 6/1/06,
5/1/07, 8/1/09 etc.
In D1837 I have data

What I need is a formula in in Cell H5 and copied across to AA5 that based
on the date in row 4 will find the matching date in range B18:B37 and return
the data associated with that date.

Thanks for all help.

 

[Juan Sanchez]

Frick

H5: =VLOOKUP(H4,$B$18:$D$37,3,0)

cheers
Juan

 

[Biff]

Another possibility:

=INDEX($D18:$D37,MATCH(H4,$B18:$B37,0))

Will return #N/A if no match is found.

Biff

 

[Frick]

Thanks for all the help.

Both Index and VLOOKUP work. However, as some of the months in the H4:AA4
have no corresponding match in the B18:B37 range it returns a value of N/A.
This I can't have as the result is part of a sum range, which is then used
elsewhere.

How can I modify the formula so that if there is no result it returns 0 or
if N/A is replaced with 0.

Thanks again.

 

[Peo Sjoblom]

You can trap for that using

=IF(ISNA(MATCH(H4,$B18:$B37,0)),0,INDEX($D18:$D37,MATCH(H4,$B18:$B37,0)))

or you can use sumif when you total

=SUMIF(Range,"<>#N/A")


--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[GasMan]

Have a look at the HLOOKUP function, though this will likely return
the next highest date, and you will need to compare the returned value
to that in row 4.

In H4:AA4 I have Dates = to start of the month

In B18:B37 I have dates that are not consecutive. Such as 1/1/06, 6/1/06,
5/1/07, 8/1/09 etc.
In D1837 I have data

What I need is a formula in in Cell H5 and copied across to AA5 that based
on the date in row 4 will find the matching date in range B18:B37 and return
the data associated with that date.

Thanks for all help.

Please remove obvious from email address if emailing.

 

[Biff]

=IF(ISERROR(INDEX($D18:$D37,MATCH(H4,$B18:$B37,0))),0,INDEX
($D18:$D37,MATCH(H4,$B18:$B37,0)))

You can do the same for the VLOOKUP:

=IF(ISERROR(VLOOKUP(H4,$B$18:$D$37,3,0)),0,........

Biff

 

[Frick]

Thank You all. All the formulas worked which leads me to a question.

Which formula is best to use, i.e. memory use, re-calc, spreadsheet size.

I have been using INDEX for most of my worksheets. This particular proforma
is about 10megs and I have to set calc to manual less I want to wash away
with coffee trips each time I change a cell.

Thanks for all help and advice.