Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Thanked 0 Times in 0 Posts

    Intersection of cells (WinXP/O2003)


    Remember i've some while ago saw something about intersection in tables rows/columns...
    - can't find it though, so apologies if question has been asked before.

    I've got a large table: 300 rows deep - 100 columns wide (columns are in sets 3, meaning 3 columns represent a set)

    I'm looking up in the table from elsewhere... - and need to find an intersection.
    First I'm finding a value in column 1 with a VLOOKUP.
    Secondly I'm looking up with a HLOOKUP in the columns
    Once found I need to read the value from the intersection.

    Can this be achieved in a single formula or should I split the process in to 3 ?
    (Lookup item 1 - read address, Lookup item 2 - read address, intersect the two - read value)

    btw. where is the INTERSECT and UNION functions located ?
    They are not visible among my Excel functions?


    PS: Wish there were a knob on the TV to turn up the intelligence. There's a knob called "brightness," but that doesn't work

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: Intersection of cells (WinXP/O2003)

    Intersect and Union are Excel VBA functions, not worksheet functions.

    You can use the INDEX worksheet function to return the cell in a certain row and certain column in a range:


    If you're looking up values in one row or column, you can use MATCH instead of HLOOKUP and VLOOKUP. For example

    =INDEX(A1:CV300,MATCH(Sheet2!A1,A1:A300,0),MATCH(S heet2!B1,A1:CV1,0))

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts