Excel check cell for string match against multiple other cells

0
votes

I have a sheet in which I need to check the value of a cell against all the values in a column in an external sheet and if it matches copy an earlier cell in the matched cells row.

This is perfect for a single cell check:

=IF(E2=(external.xlsx!D2),(external.xlsx!A2),"")

...but what if I want to check if E2 matches D2:D500 and if do copy A2:A500 respectively?

The data below may help to clarify:

MASTER SHEET
COLUMN E                           COLUMN F

2 - 0007597_halka_kamila.jpeg
3 - 0007597_halka_kamila.jpeg
4 - 0007597_halka_kamila.jpeg
5 - 0007715_komplet_elena.jpeg
6 - 0007715_komplet_elena.jpeg
7 - 0007715_komplet_elena.jpeg
8 - 0007913_podomka_erika.jpeg
9 - 0007913_podomka_erika.jpeg

EXTERNAL SHEET
COLUMN A                            COLUMN D
2 - HALKA KAMILA                    0007597_halka_kamila.jpeg
3 - HALKA KAMILA                    0007597_halka_kamila.jpeg
4 - HALKA KAMILA                    0007597_halka_kamila.jpeg
5 - KOMPLET ELENA                   0007715_komplet_elena.jpeg
6 - KOMPLET ELENA                   0007715_komplet_elena.jpeg
7 - KOMPLET ELENA                   0007715_komplet_elena.jpeg
8 - PODOMKA ERIKA                   0007913_podomka_erika.jpeg
9 - PODOMKA ERIKA                   0007913_podomka_erika.jpeg

So in the example above I need the formula in Master Sheet Column F2 to check for a match against every single cell in External Sheet Column D. If there is a match then the corresponding value from External Sheet Column A is copied to Master Sheet F2 which would hopefully give:

MASTER SHEET DESIRED RESULT
COLUMN E                            COLUMN F

2 - 0007597_halka_kamila.jpeg       HALKA KAMILA 
3 - 0007597_halka_kamila.jpeg       HALKA KAMILA 
4 - 0007597_halka_kamila.jpeg       HALKA KAMILA 
5 - 0007715_komplet_elena.jpeg      KOMPLET ELENA
6 - 0007715_komplet_elena.jpeg      KOMPLET ELENA
7 - 0007715_komplet_elena.jpeg      KOMPLET ELENA
8 - 0007913_podomka_erika.jpeg      PODOMKA ERIKA
9 - 0007913_podomka_erika.jpeg      PODOMKA ERIKA

The objective is to then copy the formula so it can run against the 500 or so rows I have. Appreciate any advice and help.

1
excelexcel-formula
It's not entirely clear what you want to achieve. Post a data sample and expected result. NOT 500 rows. – teylyn
Have updated with data to try and clarify, many thanks. – d1ch0t0my
The formula I posted does exactly that. Do you need help putting in the correct ranges? It should be fairly obvious. – teylyn
I edited my answer. – teylyn

1 Answers

1
votes

Would a lookup work for you? The formula in G1 is

=IFERROR(INDEX($A$1:$A$10,MATCH(E1,$D$1:$D$10,0)),"")

copied down.

Edit: this is for the data as described in your edited question. Starting in F1 and copy down. If you start in F2, change the Match(E1.... to Match(E2....

=IFERROR(INDEX(external.xlsx!A:A,MATCH(E1,external.xlsx!D:D,0)),"not found")

enter image description here