0

In one sheet, I have two lists of numbers:

List 1 List 2  
12865 4523  
1239  1392  
5467  3942  
5021  2935  
32012 99945

and so on...

In a second sheet, I have the same numbers as in List 1 on the first sheet, but in a different order, and a blank second list.

List 1 List 2  
1239  
5467  
12865  
32012  
5021

I know this is a pretty basic problem, but is there a way to automate so excel looks at the numbers in list 1 and populates list 2 based on the matching numbers?

Thank you for any help!

cybernetic.nomad
  • 6,100
  • 3
  • 18
  • 31
BrainStormer
  • 41
  • 4
  • 2
    VLOOKUP, INDEX/MATCH, XLOOKUP... to name a few functions that will do this. – Scott Craner Jun 21 '23 at 15:20
  • E.g. a simple Excel formula in Sheet2!B2 *(with newer dynamic features )* - `=INDEX(Tabelle1!B2:B6,MATCH(Tabelle2!A2:A6,Tabelle1!A2:A6,0),1)`, where `MATCH` returns an array of row numbers found when matching Sheet2's (sorted) column A values with Sheet1's unsorted A values; these row results build the row indices for the B values to be returned via `INDEX`. – T.M. Jun 21 '23 at 18:09
  • _"...populates list 2 based on the matching numbers."_ I don't understand what this means. You should add an example of what result you desire. – Marc Jun 21 '23 at 18:14

1 Answers1

1

If Sheet1 contains List1 and List2 in the columns A and B and Sheet2 contains List1 in the column A, formula in the Sheet2!B2 can look like this:

=IFERROR(VLOOKUP(Sheet2!A2,Sheet1!A2:B6,2, FALSE),"")

IFERROR filters error values and replace them with blanks

After entering this formula into Sheet2!B2 double click on the fill handle to expand it to all column automatically.

Glen M
  • 11
  • 1