Vlookup, index, match?

[Phyllis]

Here's what I need to do: I need a formula that will look
at the SSN on sheet one match it to the SSN on sheet two,
when it finds a match then find the value in column R and
based on certain criteria, I need to add an if statement.
I'm not sure how to or if I can do a vlookup and an if
statement in the same formula.

example:

=vlookup(A1,Sheet2$A1:R1000,18,false), then based on the
data found, I need =if(Sheet2$R?="T","TMR","REG")

I know this is not the proper way to do this, and that's
where I need help. What is the easiest way to accomplish
this?

 

[Biff]

Hi!

Try this:

=IF(ISERROR(vlookup(A1,Sheet2$A1:R1000,18,false)),"",IF
(vlookup(A1,Sheet2$A1:R1000,18,false)="T","TMR","REG"))

Biff

 

[Bob Phillips]

Phyllis,

air-coded

=IF(ISNUMBER(MATCH(A1,Sheet2!$A1:A1000,0)),IF(INDIRECT("Sheet2!$R"&MATCH(A1,
Sheet2!$A1:A1000,0))="T","TMR","REG"))

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Biff]

Ooops!

slight correction, don't forget the "!" in the Sheet2
reference:

=IF(ISERROR(vlookup(A1,Sheet2!$A1:R1000,18,false)),"",IF
(vlookup(A1,Sheet2!$A1:R1000,18,false)="T","TMR","REG"))

Biff

 

[Phyllis]

Thanks Biff, it wasn't perfect, but it was enough to point
me in the right direction and I was able to figure it out
and make it work. You guys are awesome. thanks again.