Refencing a Table & Lookup

  • Thread starter Thread starter shutterbug1
  • Start date Start date
S

shutterbug1

I'm working on a simple tool where a person could return a
value base on the intersection of two inputs.

For example: (Imagine a mileage array where you) Depart
from Los Angeles and arrive in Detroit. The result would
be ____.

Anchorage.......Detroit.......Los Angeles
Anchorage 0 2,503 1,800
..
..
Los Angeles 1,020 0

I've tried all three LOOKUP formulas without much success.
 
Well, thanks to Tushar, we now know how you can use XL's intersection
operator(inside joke), or you could use it (intersection operator) in
combination with labels in formulas.
The intersection operator is in reality, nothing but a space.

Check this out as an example of using labels in formulas in conjunction with
intersection operators:

http://tinyurl.com/p4md

OR

http://www.google.com/[email protected]&oe=UTF-8&output=gplain

Another approach is to name each individual column as a range and do the
same for each individual row.
In your example, select the Anchorage column as far down as you have data,
then click in the name box, enter "Anchorage" (no quotes), and hit <Enter>.
Do the same for the other columns.

Then select and name the rows, putting an X in front on the city names to
differentiate them from the similar column names.

Now enter a column city name in A1.
Enter a row city name in A2 (don't forget the X).
Enter this formula in A3 and the mileage will be displayed:

=INDIRECT(A1&":"&A1) INDIRECT(A2&":"&A2)

Notice the "space" between the 2 Indirect functions !
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
 
Back
Top