Two way lookup where row isn't know?

[MrPez]

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?

 

[AlanY]

something like this?

Book1
	A	B	C	D	E
1
2
3
4
5
6
7	Word1			Word2
8
9
10
11
12
13	4
Sheet1

Cell Formulas
Range	Formula
A13	=MATCH("Word2",INDEX(A1:E10,MATCH("Word1",A1:A10,0),0))

 

[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

 

[AlanY]

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

Book1
	A	B	C	D	E
1
2
3
4
5
6
7	Word1			Word2
8
9
10
11
12
13	4
Sheet1

Cell Formulas
Range	Formula
A13	=MATCH("Word2",INDEX(A1:E10,MATCH("Word1",A1:A10,0),0))

 

[MrPez]

Sorry, yes that works great
Thanks!

 

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

 

[MrPez]

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

 

[AlanY]

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

 

[Fluff]

@AlanY
Only the inner Match in your formula is an exact match. The outer Match does not have the final argument specified

 

[AlanY]

yes, got it.
thanks