Find value from one array in another array and return another cell

[davidmj54]

List one has many names in column A with a sales total in column B

Dave - $200

Tim - $300

John - $150

List two just has names in Column A and no sales (Not all of the name are in both list)

Dave

John

Sue

Alex

If list two contains a name from list one then I want to put return the sales in list two column B.  

Any idea what function I need?

I have office 365

Thanks,

Dave

[#2]

@davidmj54 

= XLOOKUP( Name, Source[Name], Source[Amount], "" )

This assumes you have a modern dynamic array version of Excel which is gradually being rolled out to Office 365 users.  At a more specific level it assumes the source data is held in an Excel Table (so that it resizes as data is added and provides structured references).  The other table needs to be a simple range but with defined Names to identify the data.

The XLOOKUP function is intended to replace virtually all existing lookup strategies.  Since 'Name' is a multicell range the function will return the entire column of results as a dynamic range.  XLOOKUP defaults to an exact match and the 4th parameter is the value to return when the lookup value (name) is not found.  It may be as simple as a blank (as here) or could be an entirely separate calculation such as a lookup to an alternative table.

[#3]

@davidmj54 

dave, as @Peter Bartholomew suggested that could be XLOOKUP on most modern Excel or more traditional VLOOKUP or INDEX/MATCH. The question is are your lists in different worksheets in one workbook or they are in separate workbooks?

[#4]

@Sergei Baklan Thank you both for your help.  I will have to look at the XLOOKUP, I am not familiar with it yet.  I am using office 365 at work and I assume that it is the latest version but I am not 100% sure that it has the update that was mentioned.

I have the raw data with names and sales totals in Sheet 1 and my matching list is in Sheet 2.  The reason the names in the list are different is that some employees are no longer with the company and there are some new ones.

For Example:

Sheet 1

  A             B

Dave      $400

Steve     $250

Tim        $456

Sara       $600

John      $325

Sheet 2

Dave

Alex

Tim

John

--------------------------

The solution in sheet 2 should look like this:

  A           B

Dave    $400

Alex     

Tim      $456

John     $325

Thoughts?

Thanks again for your help.

[#5]

@davidmj54 

My guess is that a corporate license is most likely on semi-annual update and you would need IT intervention if you were to be set up to evaluate dynamic array solutions.  @Sergei Baklan will be able to advise further on this.

Without dynamic arrays, I personally would use a Table for the report as well as the source data so that your formulas are readable and propagate to fill the entire field (note: this is not the traditional way of working and has only been available since 2007)

= IFERROR( VLOOKUP( [@Name], Source, 2, FALSE ), "" )

or

= IFERROR( INDEX( Source[Amount],

MATCH( [@Name], Source[Name], 0 ), "" )

If you need solutions that use direct referencing, rather than tables, I am sure others will be able to help.

[#6]

@Peter Bartholomew 

Yes, usually corporate environment is on Semi-Annual channel. And usually IT has nothing against moving some limited number of people on Monthly channel if rules of the game are defined for them.

However, both have no Dynamic Arrays. So far DA is only for Insiders Fast and on Monthly Targeted channel (aka Insiders Slow). Recently DA was deployed for Excel Online, but Microsoft removed it back, something went wrong. I don't know what are Microsoft plans, but expect general availability of Dynamic Arrays in an year or so, more close to next Christmas.

Using of XLOOKUP without Dynamic Arrays? - Yes, that's a nice toy, but IMHO, only with DA you may find all power of the function.

@davidmj54 , without DA, XLOOKUP and tables the function in B1 of Sheet2 could be

=IFNA(VLOOKUP(A1,Sheet1!$A:$B,2,FALSE),"")

and drag it down.