Formula to display one bit of info based on two others matches

[md55mrd]

I have a spreadsheet with some data and addresses. I am wanting to do a formula where if sheet1 b2 (address) = sheet 2 b2 through b45 address then sheet 1 a2 = displays sheet 2 a2.

If that makes any sense at all

sheet 1
A2 123 FAKE ST

sheet 2
B2 123 FAKE ST
B15 123 FAKE ST

sheet 1
A1 FAKE_123

 

[Chrisdontm]

Try this formula in cell: A2

=INDEX(Sheet2!$A$2:$B$45,MATCH(Sheet1!B2,Sheet2!$B$2:$B$45,0),1)

Then copy it down the column as far as you need to...

 

[md55mrd]

I have attempted the above formula and am still receiving a response of #NA . Are there any other suggestions?

 

[Chrisdontm]

I may not have understood your desired results...

Perhaps this is what you are wanting;

It looks like you are saying if the address in Sheet 1 B2 is within the list on Sheet 2 column B, rows 2 through 45, then put whatever is on Sheet 2 in cell A2 over into Sheet 1 cell A2.

If so, then put this formula in Sheet 1 cell A2

=IF(ISNUMBER(MATCH(Sheet1!B2,Sheet2!$B$2:$B$45,0)),Sheet2!A2,"")

If that is not it, then I would need more of an explanation...

Have a great day!