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

md55mrd

New Member
Joined
Nov 17, 2017
Messages
8
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

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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...
 
Upvote 0
I have attempted the above formula and am still receiving a response of #NA . Are there any other suggestions?
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

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