Special Lookup Formula
 

I am preparing a currency worksheet. My source sheet does not use the
exact same name each time I load the data (I have it set up to
automatically refresh the data on open). However, one thing that is
consistent in the data EVERY time is the name of the country. As a
result, I have to set up my lookup formula accordingly.

For instance, the raw data will say: United States Dollar to
Australian Dollar. The next time it is loaded it might say:
Australian Dollar to United States Dollar. There are about 50
currencies like this. Let's assume that the raw data is in the
following range: 'Raw Data'!A:B. Column B has the conversion rates.
The sheet I am using to filter the data will be set up like this:

Country Abbreviation Lookup
Australian AUD ='find A2 in 'Raw Data'!A:B and
return the row number
British GBP
Czech CZK
Danish DKK

etc.

The problem I run into is that the words I am looking for are not
WHOLE cells on the raw data sheet. Any help is much appreciated.
Thanks.

Kevin

Special Lookup Formula
 

With A1:B5 on the RawData sheet containing this list
Country Abbreviation
Australian AUD
British GBP
Czech CZK
Danish DKK

And A2:B3 on Sheet1 contains these values
United States Dollar to Australian Dollar
Australian Dollar to United States Dollar

NOTE: In your sample data, "Australian Dollar" and "United States Dollar"
each appear in 2 rows. This formula would return the row number of the first
appearance:

On the RawData sheet
C2: =MATCH(1,INDEX(--ISNUMBER(SEARCH(A2,Sheet1!$A$1:$A$10)),0),0)
Adjust the range references and copy that formula down as far as you need.

In the above example,
C2 returns row 2

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"DoooWhat" wrote:

I am preparing a currency worksheet. My source sheet does not use the
exact same name each time I load the data (I have it set up to
automatically refresh the data on open). However, one thing that is
consistent in the data EVERY time is the name of the country. As a
result, I have to set up my lookup formula accordingly.

For instance, the raw data will say: United States Dollar to
Australian Dollar. The next time it is loaded it might say:
Australian Dollar to United States Dollar. There are about 50
currencies like this. Let's assume that the raw data is in the
following range: 'Raw Data'!A:B. Column B has the conversion rates.
The sheet I am using to filter the data will be set up like this:

Country Abbreviation Lookup
Australian AUD ='find A2 in 'Raw Data'!A:B and
return the row number
British GBP
Czech CZK
Danish DKK

etc.

The problem I run into is that the words I am looking for are not
WHOLE cells on the raw data sheet. Any help is much appreciated.
Thanks.

Kevin

Special Lookup Formula
 

Ron,

Thanks for the reply. For some reason, this formula did not work. It
returned a value of #N/A. Here is the formula I typed:

=MATCH(1,INDEX(--ISNUMBER(SEARCH(R6,RawData!A:A)),0),0)

Where R6's value is "Australian". A7 on the RawData sheet is
"Australian Dollar to US Dollar". The sheet will never duplicate an
entry. That is to say, there will only be 1 line for the Australian
Dollar on each refresh. It's just that the way it is typed will be
different. Next time it might say "United States Dollar to Australian
Dollar". It seems like we are on the same page regarding my objective
here.

Special Lookup Formula
 

=MATCH(1,INDEX(--ISNUMBER(SEARCH(R6,RawData!A:A)),0),0)

You can't use an entire column reference in this manner (unless you're using
Excel 2007). Use a smaller range:

=MATCH(1,INDEX(--ISNUMBER(SEARCH(R6,RawData!A1:A100)),0),0)

Biff

"DoooWhat" wrote in message
oups.com...
Ron,

Thanks for the reply. For some reason, this formula did not work. It
returned a value of #N/A. Here is the formula I typed:

=MATCH(1,INDEX(--ISNUMBER(SEARCH(R6,RawData!A:A)),0),0)

Where R6's value is "Australian". A7 on the RawData sheet is
"Australian Dollar to US Dollar". The sheet will never duplicate an
entry. That is to say, there will only be 1 line for the Australian
Dollar on each refresh. It's just that the way it is typed will be
different. Next time it might say "United States Dollar to Australian
Dollar". It seems like we are on the same page regarding my objective
here.

Special Lookup Formula
 

Biff:

I didn't realize you couldn't make the reference that big. I changed
the reference and that fixed my problem. Thanks so much for the help,
guys!

Kevin