# 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?

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))

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

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))

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

Sorry, yes that works great
Thanks!

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)

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

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

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

yes, got it.
thanks

## User Tag List

#### Posting Permissions

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