Table matrix value swapping

[Luke777]

Hi all,

I've got a table on sheet 2 with three columns that is acting as a matrix

Column 1 contains area codes, column 2 contains a product number, and product 3 contains an alternative product number.

Using this table, I need to change information on sheet1. sheet1.Column 1 contains the area code (as on table), column 2 contains product number. I need to completely swap column 2's values to the corresponding alternative product number on the table.

There is a caveat to this - the product numbers (neither type) are NOT unique, so any matching must be done on an area code AND product code basis (which does make it unique).

I have tried to use a scripting dictionary to do this (I know how to swap a list of employee ID's to employee names for example) but I don't understand it well enough to get it to work with three columns total (as it would need two keys?)

Thanks

 

[james_martin001]

XLOOKUP in Sheet 1 so it is updated by Sheet 2 automatically?

 

[Alex Blakenburg]

I am also not sure why you are not using XLookup.
The Xlookup format is:
=XLookup(1,(AreaCode=A2)*(Product=B2), AlternateProduct, "")
where A2 is the AreaCode Lookup value, B2 the Product Lookup value

The old school way of doing it would be:
=XLookup(A2 & B2,AreaCode & Product, AlternateProduct, "")

When using the dictionary you mimic the latter method:
dictKey = A2 & B2 (you can use a delimiter but you don't need to ie A2 & "|" & B2 - it just helps if you want to check values in the watch window or immediate window)
You use this format when you load the dicitionary and again when you read from it.