0

On sheetA I have a column of names. On sheetB I have those names in a column paired with an ID.

On sheetC I want to have a new column that takes the names from sheetA, matches them with the names on sheetB and returns the relevant ID.

I've been trying to achieve this with vlookup but can't quite figure it out. So far on sheetC I have:

=xlookup(A3,SheetA!$A$1:$A$600,SheetB!$B$1:$B$600,"not found")

The result I want is as follows:

sheetA

A
John Smith
Jane Doe
Joe Blow
John Smith
John Smith

sheetB

A B
John Smith 0001
Jane Doe 0002
Joe Blow 0003

sheetC - desired outcome

A
0001
0002
0003
0001
0001

Would anyone be able to point me in the right direction?

MeltingDog
  • 14,310
  • 43
  • 165
  • 295
  • Just add the sheet reference to the first parameter of `XLookup` like so `=XLOOKUP(SheetA!A1,SheetB!A:A,SheetC!B:B,"Not Found")` – Kairu Mar 23 '23 at 01:46

1 Answers1

1

You lookup value A3 seems incorrect. Try-

=XLOOKUP(SheetA!A1,SheetB!$A$2:$A$2000,SheetB!$B$2:$B$2000)

enter image description here

Harun24hr
  • 30,391
  • 4
  • 21
  • 36
  • This is strange - I pasted in your code verbatim and still get the error `#NAME?` – MeltingDog Mar 23 '23 at 01:55
  • Can you check you have `XLOOKUP()` available to your version of excel? Or due to regional settings you need to use semicolon `(;)` instead of comma `(,)` like `=XLOOKUP(SheetA!A1;SheetB!$A$2:$A$2000;SheetB!$B$2:$B$2000)`. – Harun24hr Mar 23 '23 at 01:58
  • 1
    Yep, must be a version issue. Tried the in-browser version and it worked. Many thanks! – MeltingDog Mar 23 '23 at 02:03
  • @MeltingDog Then use index/match like `INDEX(SheetB!$B$2:$B$600,MATCH(SheetA!A1,SheetB!$A$2:$A$600,0))`. – Harun24hr Mar 23 '23 at 02:11