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
 

Some videos you may like

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
Joined
May 4, 2015
Messages
810
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
Joined
Nov 17, 2017
Messages
8
I have attempted the above formula and am still receiving a response of #NA . Are there any other suggestions?
 

Chrisdontm

Well-known Member
Joined
May 4, 2015
Messages
810
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,108,525
Messages
5,523,384
Members
409,514
Latest member
MarkZuckerberg

This Week's Hot Topics

Top