Getting multiple values using Vlookup

[Dhruva101]

I have following info in one sheet.

Program Job
ABC258
ABC564
ABV789

Another sheet has:

Program Job
ABC258 RXLDMX
ABC258 RXLWMX
ABV789 ABXM89M


In the second sheet, there are two jobs for one program. I want to do
Vlookup on the sheet 1 for the column JOB to get the job informatio
from the second sheet. But Since I can have multiple jobs per on
program and I need multiple jobs to be listed on the sheet 1, how do
get this ? Is there a way for this ?

=============================
Expected Output is

Program Job
ABC258 RXLDMX
ABC258 RXLWMX
ABC564 NA
ABV789 ABXM89M
====================================

 

[Dave Peterson]

Why not just merge the first sheet (single column) into the second worksheet
(with 2 columns).

If you want to try, this is what I would do.

First sheet (single column) is named Sheet1.
Second Sheet (2 columns) is named Sheet2.

I inserted headers in row 1 of sheet1.
In B2, I put this formula:
=match(a2,sheet2!a:a,false)

And copied down the column.

This resulted in either numbers (for matches) or #N/A (for no match).

I selected column B and did
edit|copy
edit|paste special|values

Then with column B still selected,
edit|replace
what: #n/a
with: N/A
replace all
('cause that's what you wanted to see).

Then with column B still selected, I did this:
Data|Filter|autofilter
to show the #n/a's

Selected those visible rows and copied to the bottom of sheet2 two's data.

Then I selected A:B on Sheet2 and sorted by column A (and column B).

And then back to Sheet1 to clean up column B
Data|filter|autofilter
(to remove the filter)
Then I deleted column B (I was done with it).