How to search to spreadsheets for a match and then pull data from matching row

[fozuk1609]

I would like to do the following:

Check GS1 Barcode List 1 Column H against Active Listings Column K.
If there's a match then copy GS1 Barcode List 1 Column A from the same row into Active Listings Column L in the same row

Do the same for GS1 Barcode List 2 and the Active Listing spreadsheet

Ive added a snippet of each to help. There may not be a match on these sheets but hopefully it will help with a formula.

I really appreciate any help you can offer.

[Richard Buttrey]

Hi,

The approach I'd adopt is to use a helper column say M, in the Listings sheet and check whether an item on the Active listings sheet exists in the List 1 or List 2 workbooks. If so grab the value from Column K in the List 1 or List 2 sheets, otherwise grab the value from column A in the Listings sheet

Then copy the helper column M and Paste it back to Column A as values.

e.g. in M2 of the Listings sheet copied down

=IFERROR(INDEX('GS1 Barcode List 1.xlsx'!$A:$A,MATCH(K2,'GS1 Barcode List 1.xlsx'!$H:$H,FALSE),1),A2)

When you've pasted back to column A repeat the formula and change the GS1 List 1 reference to the List 2 reference.

[fozuk1609]

Thanks for that - I wouldnt need it to add the value from column A. if it left it blank I can then see any problems areas and work out why it doesn't match. They should ALL match.

Just wanted to check if this would work in google sheets? Ive just started using it or should I go back to excel?

[Richard Buttrey]

I've not tested in google sheets but as I recall MATCH() is a standard function essentially the same as in Excel so AFAIAA that wouldn't be a problem.

Unless there's some reason not to use Excel, particularly now that in 365 it has co-authoring when used on a common server making it similar in functionality to google sheets I'd stick with Excel. On the whole I prefer find editing Excel easier, perhaps because I've used it for 20+ years. And the VBA macro coding seems easier than google's scripting.