Hello,

I am using the Column function to get the Column number from a different
seperate workbook like the following:

=COLUMN('L:\My Documents\[LookupData.xls]Sheet1'!$O$2)

The above returns 15. (LookupData is a different workbook than where this
formula is being used.)

Would anyone know if I could return the column number where a reference
value from the current workbook is contained in the workbook LookupData. For
example, I have "Apples" in CellA2 in the current workbook, how can I get the
column number in LookupData which contains the text "Apples"?

Thanks in advance!

You do not need a coulmn function to do that

Check Match function in help . Youu formula would always begin from Column A
something like this

=match("Apples",Sheet1!A3:J3,0)

Its important that you start with column A even though your range does not
begin from column "A"

"sharonm" wrote:

Hello,

I am using the Column function to get the Column number from a different
seperate workbook like the following:

=COLUMN('L:\My Documents\[LookupData.xls]Sheet1'!$O$2)

The above returns 15. (LookupData is a different workbook than where this
formula is being used.)

Would anyone know if I could return the column number where a reference
value from the current workbook is contained in the workbook LookupData. For
example, I have "Apples" in CellA2 in the current workbook, how can I get the
column number in LookupData which contains the text "Apples"?

Thanks in advance!

If "apples" is on a specific row:

=MATCH(A2,'L:\My Documents\[LookupData.xls]Sheet1'!2:2,0)

--
Biff
Microsoft Excel MVP


"sharonm" wrote in message
...
Hello,

I am using the Column function to get the Column number from a different
seperate workbook like the following:

=COLUMN('L:\My Documents\[LookupData.xls]Sheet1'!$O$2)

The above returns 15. (LookupData is a different workbook than where this
formula is being used.)

Would anyone know if I could return the column number where a reference
value from the current workbook is contained in the workbook LookupData.
For
example, I have "Apples" in CellA2 in the current workbook, how can I get
the
column number in LookupData which contains the text "Apples"?

Thanks in advance!

Can the text "Apples" be anywhere on the sheet? Any row, any column?
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"sharonm" wrote in message
...
Hello,

I am using the Column function to get the Column number from a different
seperate workbook like the following:

=COLUMN('L:\My Documents\[LookupData.xls]Sheet1'!$O$2)

The above returns 15. (LookupData is a different workbook than where this
formula is being used.)

Would anyone know if I could return the column number where a reference
value from the current workbook is contained in the workbook LookupData.
For
example, I have "Apples" in CellA2 in the current workbook, how can I get
the
column number in LookupData which contains the text "Apples"?

Thanks in advance!

Suppose you know it is in row 10, then use
=MATCH(A2,[LookupData.xls]Sheet1!$A10:IV10,0)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"sharonm" wrote in message
...
Hello,

I am using the Column function to get the Column number from a different
seperate workbook like the following:

=COLUMN('L:\My Documents\[LookupData.xls]Sheet1'!$O$2)

The above returns 15. (LookupData is a different workbook than where this
formula is being used.)

Would anyone know if I could return the column number where a reference
value from the current workbook is contained in the workbook LookupData.
For
example, I have "Apples" in CellA2 in the current workbook, how can I get
the
column number in LookupData which contains the text "Apples"?

Thanks in advance!

Thank you All. The text I am looking for is on a certain row in the workbook,
so the sugestions were very helpful. Thanks all.


"Bernard Liengme" wrote:

Suppose you know it is in row 10, then use
=MATCH(A2,[LookupData.xls]Sheet1!$A10:IV10,0)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"sharonm" wrote in message
...
Hello,

I am using the Column function to get the Column number from a different
seperate workbook like the following:

=COLUMN('L:\My Documents\[LookupData.xls]Sheet1'!$O$2)

The above returns 15. (LookupData is a different workbook than where this
formula is being used.)

Would anyone know if I could return the column number where a reference
value from the current workbook is contained in the workbook LookupData.
For
example, I have "Apples" in CellA2 in the current workbook, how can I get
the
column number in LookupData which contains the text "Apples"?

Thanks in advance!