Filtering data out of a list in Excel Sheet

Question

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))

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.

Answer 1

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':

(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:

(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:

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:

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