Two way lookup where row isn't know?
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Two way lookup where row isn't know?

  1. #1
    Board Regular
    Join Date
    Jan 2010
    Posts
    121
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Two way lookup where row isn't know?

    I need a formula to find a specific word in column A then find another word on that row, and then give me the column number that the second word is in.

    So the first part would be =MATCH("Word1",A:A,0)

    The second part would be =MATCH("Word2",Row:Row,0)
    where Row is the result of the previous formula.

    However, I'm struggling to combine the two formulae?

  2. #2
    Board Regular
    Join Date
    Oct 2014
    Location
    UK
    Posts
    3,826
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Two way lookup where row isn't know?

    something like this?

    ABCDE
    1
    2
    3
    4
    5
    6
    7Word1Word2
    8
    9
    10
    11
    12
    134

    Sheet1



    Worksheet Formulas
    CellFormula
    A13=MATCH("Word2",INDEX(A1:E10,MATCH("Word1",A1:A10,0),0))

    Last edited by AlanY; Jul 19th, 2019 at 06:26 AM.

  3. #3
    Board Regular
    Join Date
    Jan 2010
    Posts
    121
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Two way lookup where row isn't know?

    Kinda like that, but the column lookup for Word 2 must only use row 7 as there could be other instances of the same word elsewhere in A1:E10

  4. #4
    Board Regular
    Join Date
    Oct 2014
    Location
    UK
    Posts
    3,826
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Two way lookup where row isn't know?

    Quote Originally Posted by MrPez View Post
    Kinda like that, but the column lookup for Word 2 must only use row 7 as there could be other instances of the same word elsewhere in A1:E10
    that's what (MATCH("Word1",A1:A10,0)) trying to do

    Quote Originally Posted by AlanY View Post

    ABCDE
    1
    2
    3
    4
    5
    6
    7Word1Word2
    8
    9
    10
    11
    12
    134

    Sheet1



    Worksheet Formulas
    CellFormula
    A13=MATCH("Word2",INDEX(A1:E10,MATCH("Word1",A1:A10,0),0))
    Last edited by AlanY; Jul 19th, 2019 at 06:46 AM.

  5. #5
    Board Regular
    Join Date
    Jan 2010
    Posts
    121
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Two way lookup where row isn't know?

    Sorry, yes that works great
    Thanks!

  6. #6
    Board Regular Snakehips's Avatar
    Join Date
    May 2009
    Location
    Coventry UK
    Posts
    4,663
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Two way lookup where row isn't know?

    Does Alans's formula not still give a wrong answer if there is an intervening word eg Word3 in column B ????

    I'm thinking you might need to tweak as per....

    =MATCH("Word2",INDEX(A1:E10,MATCH("Word1",A1:A10,0),),0)
    Tony

    AbUsing Excel 2002 to 2013
    This line will be updated just as soon as I come up with something, original, witty, profound or interesting. Please don't hold your breath!

    Please remember - we cannot see your workbook nor read your mind.
    Help us to help you, post clear detail of what you have and what you want - from the start.

    A screen shot can save a thousand words!
    Post a screen shot with one of these: Excel Jeanie, MrExcel HTML Maker
    If posting VBA code, please use Code Tags - see: here

  7. #7
    Board Regular
    Join Date
    Jan 2010
    Posts
    121
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Two way lookup where row isn't know?

    Quote Originally Posted by Snakehips View Post
    Does Alans's formula not still give a wrong answer if there is an intervening word eg Word3 in column B ????

    I'm thinking you might need to tweak as per....

    =MATCH("Word2",INDEX(A1:E10,MATCH("Word1",A1:A10,0),),0)
    Is that just to ensure an exact lookup?
    Thanks both

  8. #8
    Board Regular
    Join Date
    Oct 2014
    Location
    UK
    Posts
    3,826
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Two way lookup where row isn't know?

    Quote Originally Posted by MrPez View Post
    Is that just to ensure an exact lookup?
    Thanks both
    TBH, i'm not sure of Snakehips' suggestion or what it's trying to solve.

    the match() in post#4 is looking for the exact match anyway

  9. #9
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,665
    Post Thanks / Like
    Mentioned
    445 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Two way lookup where row isn't know?

    @AlanY
    Only the inner Match in your formula is an exact match. The outer Match does not have the final argument specified
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  10. #10
    Board Regular
    Join Date
    Oct 2014
    Location
    UK
    Posts
    3,826
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Two way lookup where row isn't know?

    yes, got it.
    thanks
    Last edited by AlanY; Jul 19th, 2019 at 08:37 AM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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