INDEX or VLOOKUP + extra formula/value

**ricoandquesa** · 11-29-2007, 08:57 PM

Hi there

=INDEX('Call Rates'!C:C,MATCH(E5,'Call Rates'!B:B,FALSE),1)

or

=VLOOKUP(E5,'Call Rates'!B:C,2,0)

*Which one is better?

The only problem with both of these is that I can't refence a formula in another cell, I get a 0 value. The formula that I would like to reference relates to column D where the INDEX or VLOOKUP formula is located.

*Can either of them incorporate the following -

=MIN(2,MOD(D31,"0:05")*864)+INT(D31/"0:05")*2

and

=ROUNDUP(D100*2880,0)*0.08

Thank you Quesa

**shg** · 11-29-2007, 11:02 PM

Regarding the first issue, I prefer the second option for its brevity.

In the first option, the use of False as the match_type argument is spurious. The match_type enumerations are -1, 0, or 1 for match descending, exact, or ascending respectively.

Regarding the second issue, I can't guess what you're trying to do.

**ricoandquesa** · 11-29-2007, 11:39 PM

I have attached my spreadsheet.

See the cells r12-r18 need to give the same results as j12-j18, the same with r155 & j155.

At the moment I am getting a 0 value.

Thank you

**shg** · 11-30-2007, 12:27 AM

The formulas in J12:J18 return zero because the lookup result ('Call Rates'!C8) is zero. And that is zero because the precedent to its formula ('Call Rates'!D31) is blank.