Vlookup Help Needed

[jdperez]

Hi,
I'm hoping this is an easy solution and I'm just not thinking right today.

I need to copy data over only if a column doesn't match a specific value in column A.

My reference is Column A
Column B needs to be compared to Column A

If Column B does not match any of the values in Column A then I need it to copy it's value for B & C.

Hope that makes sense, any help would be appreciated.

Thanks!

Jason

[ibt]

I hope I understood your question correctly, not too sure what you meant exactly by "...to copy it's value for B & C.". But attached is a solution using the formula:

=IFERROR(IF(MATCH(B2,$A$2:$A$14,0)>0,""),B2)

If this isn't what you mean, please clarify and I can try and readjust...

[AlKey]

Not totally clear how the non-matching values should be positioned though

in C2 and copy down

=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$14)/(COUNTIF(A$2:B$14,B$2:B$14)=1),ROWS(B$2:B2))),"")

Row\Col	A	B	C
1	Reference	Comparison	If no match
2	9	9	7
3	8	8	11
4	8	7	12
5	6	6	13
6	5	5
7	4	4
8	3	3
9	2	2
10	1	1
11	0	0
12	1	11
13	2	12
14	3	13

[jdperez]

Thanks for the responses.
Let me try to clarify.
Column A has the values that I need to use.
Column B is a mix of values that i need to separate.
Column C describes the values in Column B.

For example

if the value in B = 25 and 25 isn't in column A. I need it to give me the value of B and it's description in C. End value should be 25 Agroeconomics

Row/Col	A	B	C	D	E	F
1	9	25	Agroeconomics	25	Agroeconomics
2	14	24	Biology
3	15	26	Health	26	Health
4	16	27	English	27	English
5	24	28	Math	28	Math

[ibt]

jdperez,

Thanks for the clarification. Attached is a solution doing what you described using the formulas:

=IFERROR(IF(MATCH(B1,$A$1:$A$6,0)>0,""),B1)
and
=IF(D1="","",C1)

I am still trying to wrap my head around the impressive solution that AlKey posted earlier, but this should do what you described. If not let us know.

[FlameRetired]

Using the data in post #4 and this formula in D1 filled down and across:

Please Login or Register  to view this content.

I get this.

Row\Col	A	B	C	D	E
1	9	25	Agroeconomics	25	Agroeconomics
2	14	24	Biology
3	15	26	Health	26	Health
4	16	27	English	27	English
5	24	28	Math	28	Math