If Cell Matches Item from Cell Range then Display Item from Different Cell Range

If Cell Matches Item from Cell Range then Display Item from Different Cell Range

Example: Cell B1 Contains "ABC123" If "ABC123" Matches "ABC123" From a Cell Range/Defined Name "Office Number" Then Output Matching Data From DIFFERENT Cell Range/Define Name "Location City and State.


D1:D5500 Named: "Office Number"
E1:E5500 Names: "Location City and State

Anyone Have an Idea?

Hi, welcome to the forum

I *think* you need to look at the INDEX/MATCH combo for this, but not really sure I undertand what you want.

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

Use the vlookup function. http://www.techonthenet.com/excel/formulas/vlookup.php

Originally Posted by kikialiena

D1:D5500 Named: "Office Number"
E1:E5500 Names: "Location City and State

Those are invalid named ranges. Or, are they ranges within a defined named table?

See Attached Dummy Doc (Kikialiena).

Those are Ranges within a Defined Name Table.

Title:

Should then Output: John Smith New York NY

INDEX/MATCH will work, but Alan's vlookup is simpler...
=VLOOKUP($B$2,$C$1:$D$5,2,0)

There is no defined table in your file.