Build VLOOKUP list from a range

[spiker7221]

I want to search the data in a column, and create a new column of just the differences in that data.
Example.......
Column B has a list of say...types of cats. The column will have several rows with duplicate cat types. I want to populate a new column C with a shorter list of cat types without any duplicates. I want to use column C to run a VLOOKUP and compare it to column B and when it finds a certain value, it will prefix it with data from another column. The reason for this formula is the actual data in column B will change weekly. Basically wanting to automate creating a VLOOKUP range.

 

[Aladin Akyurek]

I want to search the data in a column, and create a new column of just the differences in that data.
Example.......
Column B has a list of say...types of cats. The column will have several rows with duplicate cat types. I want to populate a new column C with a shorter list of cat types without any duplicates. I want to use column C to run a VLOOKUP and compare it to column B and when it finds a certain value, it will prefix it with data from another column. The reason for this formula is the actual data in column B will change weekly. Basically wanting to automate creating a VLOOKUP range.

Let B2:B15 house the cat types.

In C1 enter: #List#

C2, control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(B$2:B$15,MODE(IF(B$2:B$15<>"",
    IF(ISNA(MATCH(B$2:B$15,C$1:C1,0)),
      MATCH(B$2:B$15,B$2:B$15,0)*{1,1})))),"")

 

[T. Valko]

I want to search the data in a column, and create a new column of just the differences in that data.
Example.......
Column B has a list of say...types of cats. The column will have several rows with duplicate cat types. I want to populate a new column C with a shorter list of cat types without any duplicates. I want to use column C to run a VLOOKUP and compare it to column B and when it finds a certain value, it will prefix it with data from another column. The reason for this formula is the actual data in column B will change weekly. Basically wanting to automate creating a VLOOKUP range.

You lost me on the VLOOKUP stuff.

Here's how you can extract the unique TEXT values from a list.

Book1
	B	C
2	Cat9	Cat9
3	Cat3	Cat3
4	Cat9	Cat4
5	Cat4	Cat10
6	Cat9	Cat2
7	Cat4	Cat7
8	Cat10	_
9	Cat2	_
10	Cat10	_
11	Cat3	_
12	Cat7	_
13	Cat7	_
14	Cat10	_
15	Cat3	_
Sheet1

Assumes no empty cells within the data range of column B.

Enter this formula in C2:

=B2

Enter this array formula** in C3:

=LOOKUP("zzz",CHOOSE({1,2},"",INDEX(B$2:B$15,MATCH(0,COUNTIF(C$2:C2,B$2:B$15),0))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

 

[spiker7221]

Thanks T. Valko that worked perfectly. it will save me a lot of time keying in the values every week!

You rock!

 

[T. Valko]

Thanks T. Valko that worked perfectly. it will save me a lot of time keying in the values every week!

You rock!

You're welcome. Thanks for the feedback!