Two way lookup where row isn't know?

MrPez

Board Regular
Joined
Jan 28, 2010
Messages
128
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?
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
something like this?


Book1
ABCDE
1
2
3
4
5
6
7Word1Word2
8
9
10
11
12
134
Sheet1
Cell Formulas
RangeFormula
A13=MATCH("Word2",INDEX(A1:E10,MATCH("Word1",A1:A10,0),0))
 
Last edited:
Upvote 0
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
 
Upvote 0
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
ABCDE
1
2
3
4
5
6
7Word1Word2
8
9
10
11
12
134
Sheet1
Cell Formulas
RangeFormula
A13=MATCH("Word2",INDEX(A1:E10,MATCH("Word1",A1:A10,0),0))
 
Last edited:
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
@AlanY
Only the inner Match in your formula is an exact match. The outer Match does not have the final argument specified
 
Upvote 0
yes, got it.
thanks
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,293
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top