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

#### md55mrd

##### New Member
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

### Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

#### Chrisdontm

##### Well-known Member
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

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

#### Chrisdontm

##### Well-known Member
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!

Replies
1
Views
34
Replies
4
Views
85
Replies
2
Views
68
Replies
1
Views
37
Replies
2
Views
73