INDEX function need to have col reference to be formula
 

This works quite nicely:
=INDEX('Price List'!$E:$J,MATCH($E20,'Price
List'!$B:$B,0)+ROW()-ROW(),6)

But

the last cell reference of a value in column "6" in the above formula
needs to be a calculated cell within this formula.
In this case: F3*(1+I3)

Reason being: It seems that the value in column 6 in the range is not
present when the ODBC completes its job from which the data is
extracted. I'm hoping that this calculation can occur in the cell
where the above formula is located.

So to summarize, can I replace the "6" in the above INDEX function with
the result of calculating F3*(1+I3) ?

TIA for any ideas.

Pierre

INDEX function need to have col reference to be formula
 

Hi!

The short answer to your question is yes. Did you try it?

Don't know why you're using this:

+ROW()-ROW()

It's superfluous.

Biff

"Pierre" wrote in message
ups.com...
This works quite nicely:
=INDEX('Price List'!$E:$J,MATCH($E20,'Price
List'!$B:$B,0)+ROW()-ROW(),6)

But

the last cell reference of a value in column "6" in the above formula
needs to be a calculated cell within this formula.
In this case: F3*(1+I3)

Reason being: It seems that the value in column 6 in the range is not
present when the ODBC completes its job from which the data is
extracted. I'm hoping that this calculation can occur in the cell
where the above formula is located.

So to summarize, can I replace the "6" in the above INDEX function with
the result of calculating F3*(1+I3) ?

TIA for any ideas.

Pierre

INDEX function need to have col reference to be formula
 

Biff wrote:
Hi!

The short answer to your question is yes. Did you try it?

Don't know why you're using this:

+ROW()-ROW()

It's superfluous.

Biff

Biff, thanks for the sanity check. ;-)

The formula works well as =INDEX('Price List'!$E:$J,MATCH($E20,'Price
List'!$B:$B,0),6)
The next row is
=INDEX('Price List'!$E:$J,MATCH($E20,'Price List'!$B:$B,0)+1,6)
=INDEX('Price List'!$E:$J,MATCH($E20,'Price List'!$B:$B,0)+2,6)
etc.
however, we still need to multiply the value in column F by the value
in coulmn I at the row located by the INDEX/MATCH. This would appear
where a column 6 would be if it existed. Attempts at nomenclature have
fizzled in this heat.

Thanks for your interest.

Pierre