0

Lets say we have two columns,

COLUMN1         COLUMN2
BATMAN          1
SUPERMAN A/S    2
ROBINE          3
Aquaman 3D      4

Now I wish to have a new list in Left Column, from which I can import data from the original mass data. Such that

 COLUMN1         COLUMN2
    BATMAN A/S      1
    AQUAMAN         4

And I want excel to search the original list and provide the required value in the second column.

I have also attached my excel snapshot with this question. I have been trying to use the following formula, but it does not give me what I want,

=INDEX($B$1:$B$5;MATCH(D2;$A$1:$A$5;1);COLUMN($A$1))

enter image description here

The main problem here is that the name in the original list is "not exactly the same" as the second list, therefore I wish excel to select even a partial match, but everytime I insert "-1" or "1" into the above formula, it doesnt work. Is there any other formula which I can use?

Any help would be really great. Thank You.

1
  • I have edited the answer and added how to make excel skip the same name. Have a look :) – Sanu_012 Apr 30 '20 at 14:08
2

This is something of a problem when trying to search for not an exact match and extracting its value. You have got total of 4 columns. First two columns (say A and B) are the reference columns from which data is to be extracted. So, here we go:

In the first column (C) of the last two columns (say C and D), you will enter the "non-exact" value and in the second column (D) you want the match to be extracted from the reference columns.

Click on a cell in column D where you want to extract the value from column B and enter this =VLOOKUP(D2&"*",$A$1:$B$5,2,FALSE); hit 'Enter':

enter image description here

(Drag down the crosshair from the edge of that cell to apply same effect in other cells of the column)

In the given formula, D2 is the cell containing the partial string, A1:B5 is the data range and 2 indicates to lookup the value in the second column of the range (that is column B). (You can change/adjust them as per your need, but for your convenience, I have made this excel table matching exactly yours.)

Now, when you hit 'enter' with a partial search string like "SUPE", it extracts the value from column B and displays the value at column D. See this:

enter image description here

(when I searched for "SUPE" it gave the value from column B that corresponds to 'superman')

Similarly, searching 'aqu' instead of 'AQUAMAN 3D' gives this:

enter image description here

EDIT: Since you asked for:

Is it possible to stop excel from choosing the same name again twice?

Yes, it is indeed possible and I have found out a way that's very simple. You just need to add an extra column, let's call it Final D, where the Final filtered results will appear. After creating the column, enter this into the first cell (here F2):

=IF(COUNTIF($E$1:E1,E2)>0,"SKIPPED",E2)

Now, drag from the edge of that cell all way down to do the same with entire column (Final D). Here they are with only the unique names and thus we have made excel stop from choosing the same name twice. See this:

enter image description here

This is it. Try this and tell how it does :)

3
  • Great thank you. It works like a charm. :) – MathCurious314 Apr 30 '20 at 11:33
  • Not to make it more complicated, but is it possible to stop excel from choosing the same name again twice, even if it is a partial match? Because lets say I search for a name, but it doesnt exist, but instead of excel choosing some other partial match, can excel just leave it blank? – MathCurious314 Apr 30 '20 at 11:34
  • 1
    Yeah, that is perhaps possible by making use of VBA but I think it'll get more complicated for you. And if you search for a name that doesn't exist, excel will leave the cell blank or #N/A. If this worked for you, i'll be glad if you mark this as correct answer :) – Sanu_012 Apr 30 '20 at 11:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.