Altering formula

[JaybeeinTekTips]

Hi all,

I have a spreadsheet with two sheets, where the first sheet has a column (Product Profit) that relies on a cell in sheet 1-1 for a value as follows:

=((I2-G2*'Sheet 1-1 - Table 1'!C2))

On Sheet 1-1, Column B and C are Product and Cost respectively, sample data:

Product Cost
Aciclov 1.45


I'd like to change the fomula so that instead of referring to C2, it queries the E column on Sheet 1, matches it with the Product column (B) on Sheet 1-1 and uses the corresponding value in the Cost column (C). I would like ALL formalae in the column to follow the same format (which I hope they will if we copy formats)

Many thanks,


Jaybee.

 

[SkipVought]

hi.
[tt]
=INDEX('Sheet 1-1 - Table 1'!C2,Match(Sheet1!E2,'Sheet 1-1 - Table 1'!B:B,0),1)
[/tt]
This is a LOOKUP, not a QUERY. Query has an entirely different meaning in Excel.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[JaybeeinTekTips]

Ok, how would I perform the lookup? Does it matter if it's cross-tab?

 

[SkipVought]

how would I perform the lookup

Did you see the formula?

Doing lookups in a crosstab can be more of a challenge. Please explain the data range, where the lookup range is and what range would define the return values.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]