Formula to return same cell on different tab

[nwd9s]

Hello-

I have an excel file with one tab a list of leasing clients (looks at them over time so I know when the leases end), and another tab that lists the location of the property. For example, if the lease was for building Alpha in Ohio, tab lessee would have Alpha in cell A13 and tab location would have Ohio in cell A13.

In a summary sheet when I type Alpha, I want to lookup the location. Is there a formula that could do this, perhaps match the Alpha to the lessee tab, then lookup that cell on the location tab?

Thanks,
nwd9s

 

[T. Valko]

Hello-

I have an excel file with one tab a list of leasing clients (looks at them over time so I know when the leases end), and another tab that lists the location of the property. For example, if the lease was for building Alpha in Ohio, tab lessee would have Alpha in cell A13 and tab location would have Ohio in cell A13.

In a summary sheet when I type Alpha, I want to lookup the location. Is there a formula that could do this, perhaps match the Alpha to the lessee tab, then lookup that cell on the location tab?

Thanks,
nwd9s

Try something like this...

A1 = Alpha

=INDEX(Location!A:A,MATCH(A1,Lessee!A:A,0))

 

[nwd9s]

Thanks for your help. This was my idea and what I was working on. My issues is with the Match part of the formula. The Lessee name tab is by month, and would have a lot of the same entry Alpha over the month of the lease. The problem is I can't use the range A:A because new leases start over the time horizon so I need to use the whole range which is A:BD. When I change the range I get an error '#N/A'. Is this error due to the number of matches or the size of the range, ect? Any ideas on how to fix this?

Thanks,
Nathan

 

[T. Valko]

Thanks for your help. This was my idea and what I was working on. My issues is with the Match part of the formula. The Lessee name tab is by month, and would have a lot of the same entry Alpha over the month of the lease. The problem is I can't use the range A:A because new leases start over the time horizon so I need to use the whole range which is A:BD. When I change the range I get an error '#N/A'. Is this error due to the number of matches or the size of the range, ect? Any ideas on how to fix this?

Thanks,
Nathan

The lookup_array argument of MATCH must be a one dimensional array (single row or column).

So, it sounds like you nedd to find the correct column to use. How would you know which column to look in?