# Thread: Two way lookup where row isn't know? Thanks:  1 Post #5311956 (1) Likes: 0

1. ## 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?  Reply With Quote

2. ## 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))  Reply With Quote

3. ## 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  Reply With Quote

4. ## Re: Two way lookup where row isn't know? Originally Posted by MrPez 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 Originally Posted by AlanY 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))  Reply With Quote

5. ## Re: Two way lookup where row isn't know?

Sorry, yes that works great Thanks!  Reply With Quote

6. ## 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)  Reply With Quote

7. ## Re: Two way lookup where row isn't know? Originally Posted by Snakehips 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  Reply With Quote

8. ## Re: Two way lookup where row isn't know? Originally Posted by MrPez 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  Reply With Quote

9. ## 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  Reply With Quote

10. ## Re: Two way lookup where row isn't know?

yes, got it.
thanks  Reply With Quote

## User Tag List

#### Tags for this Thread

column, find, part, row, word #### Posting Permissions

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