Conditional formula with text

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

Hello, I'm trying to put together a formula in one worksheet that is
dependent on text existing in another worksheet.

Sheet 1
A1=Kentucky B1=(formula)

Sheet 2
A1=1 B1=Oregon
A2=2 B2=Kentucky
A3=3 B3=Arizona

Basically, I need to calculate a formula in Sheet 1 where if Sheet 2
contains "Kentucky" it would return the value in the "A" column next to
"Kentucky" in Sheet 2, which is in this case "2".

Can you help me put this formula together? Thanks!

Mike
 
An index/match should serve you well, you can use it to return any col to the
left or right of the match col

In Sheet1,
In B1: =INDEX(Sheet2!A:A,MATCH(A1,Sheet2!B:B,0))
Copy down

Or, with an error trap bolted on, in B1:
=IF(ISNA(MATCH(A1,Sheet2!B:B,0)),"",INDEX(Sheet2!A:A,MATCH(A1,Sheet2!B:B,0)))
Success? hit the YES below
 
Back
Top