When I use the following formula:
=HLOOKUP(A11,INDIRECT("'"&$A11&"'!D2:H104"),3,FALS E)
with a lease number 003403 - everything is fine and the correct data is
returned.
When I use the formula with lease number 003403A, I received #NA in every
cell.
Is there a way to HLOOKUP an alpha numeric field with the formula I'm using?
Thanks,
Kelly

On Jun 6, 7:37 pm, Kelly wrote:
When I use the following formula:
=HLOOKUP(A11,INDIRECT("'"&$A11&"'!D2:H104"),3,FALS E)
with a lease number 003403 - everything is fine and the correct data is
returned.
When I use the formula with lease number 003403A, I received #NA in every
cell.
Is there a way to HLOOKUP an alpha numeric field with the formula I'm using?
Thanks,
Kelly

Kelly,

what values do you have in D2:D104?

Kostis Vezerides

Kelly

The HLookup formula, with the fourth argument False, as you have it,
should work with alphanumeric data. #NA is the response you should
get if there is not an exact match of A11 in the column D of the range
identified by your Indirect(...) function; so, it seems likely that
there is just not a match of A11 .
Good luck.

Ken
Norfolk, Va



On Jun 6, 12:47 pm, vezerid wrote:
On Jun 6, 7:37 pm, Kelly wrote:

When I use the following formula:
=HLOOKUP(A11,INDIRECT("'"&$A11&"'!D2:H104"),3,FALS E)
with a lease number 003403 - everything is fine and the correct data is
returned.
When I use the formula with lease number 003403A, I received #NA in every
cell.
Is there a way to HLOOKUP an alpha numeric field with the formula I'm using?
Thanks,
Kelly

Kelly,

what values do you have in D2:D104?

Kostis Vezerides

The D2:H4 is the following (column B content headings)
Store ID
Lessor
City
State
Abstraction Category
Lease term (months)
Possession date
Assignment Date
Lease commencement date
Rent commencement date
Term end date
Early termination notice date
Early termination date
Termination penalty
Early termination language
Early termination interpretation
Legal Entity
Type of Facility
Security deposit
Square feet
Fixed rent language
Fixed rent interpretation
Prorated rent, if any
First full month rent
Number of free months rent
Type(s) of rent escalations
Fixed rent escalation dates
Fixed rent escalation amounts
Notice requirements (months/days)
Notice date
CPI rent language
CPI rent interpretation
CPI information
Percent rent language
Percent rent interpretation
Percent of rent %
Percentage rent dates
Percentage rent hurdles
Common area rents
Lowest allowed rents
Type of T allowance
T allowance lease language
T allowance lease interpretation
T allowance amount
Transfer of ownership date
Purchase price at end of lease
Lease end language
Lease end interpretation

with the data across columns D through H.
Each tab is a lease number that is not always just numeric and can be
003403A. And it's when I add the letter at the end of the lease I
consistently receive the #NA response in all of my columns.
I hope that answers your question so you can answer mine :)
Kelly

"vezerid" wrote:

On Jun 6, 7:37 pm, Kelly wrote:
When I use the following formula:
=HLOOKUP(A11,INDIRECT("'"&$A11&"'!D2:H104"),3,FALS E)
with a lease number 003403 - everything is fine and the correct data is
returned.
When I use the formula with lease number 003403A, I received #NA in every
cell.
Is there a way to HLOOKUP an alpha numeric field with the formula I'm using?
Thanks,
Kelly

Kelly,

what values do you have in D2:D104?

Kostis Vezerides

The worksheet label (e.g. 003402A) is set up exactly as the A11 cell. The A
column in formatted as text. The formulas in the columns work for lease
003402 but not lease 003402A.
Thanks
Kelly

" wrote:

Kelly

The HLookup formula, with the fourth argument False, as you have it,
should work with alphanumeric data. #NA is the response you should
get if there is not an exact match of A11 in the column D of the range
identified by your Indirect(...) function; so, it seems likely that
there is just not a match of A11 .
Good luck.

Ken
Norfolk, Va



On Jun 6, 12:47 pm, vezerid wrote:
On Jun 6, 7:37 pm, Kelly wrote:

When I use the following formula:
=HLOOKUP(A11,INDIRECT("'"&$A11&"'!D2:H104"),3,FALS E)
with a lease number 003403 - everything is fine and the correct data is
returned.
When I use the formula with lease number 003403A, I received #NA in every
cell.
Is there a way to HLOOKUP an alpha numeric field with the formula I'm using?
Thanks,
Kelly

Kelly,

what values do you have in D2:D104?

Kostis Vezerides