Comparing 2 Columns

[tstoneh]

Is there a way to compare two columns with the same information in different spreadsheets and then put a formula in the column if they agree -- not just a true of false?

 

[Zack Barresse]

could be, if text, must be in quotes, like... "text here"

what is your data layout:

1 - what book/sheet is your formula going into?

2 - what cell is your formula going into?

3 - what cell are you referencing (looking up)?

4 - what book/sheet is your data in?

5 - what range is your data in?

6 - in your data range (eg a1:c100), which column of info do you want?

 

[tstoneh]

Here it is.

pivot Data.xls
	A	B	C	D	E	F
2	12466	HOUSING,GEARBOX,KNE	SumofExchangeGrossSaleAmt	1,569	1,568.63	#N/A
3			SumofExchangeGrossSaleQty	1
4	12467	HOUSING,GEARBOX,KNE	SumofExchangeGrossSaleAmt			#N/A
5			SumofExchangeGrossSaleQty
6	12476	SPRINGASSEMBLY,SHO	SumofExchangeGrossSaleAmt	-		#N/A
7			SumofExchangeGrossSaleQty	-
8	12477	ISOLATOR,SHOCK,LIQU	SumofExchangeGrossSaleAmt			#N/A
9			SumofExchangeGrossSaleQty
10	16777	GEARCLUSTER	SumofExchangeGrossSaleAmt	-		#N/A
11			SumofExchangeGrossSaleQty	-
12	16778	GEAR,SPUR	SumofExchangeGrossSaleAmt	-		#N/A
13			SumofExchangeGrossSaleQty	-
Sheet3

Maybe this will help. I hope I did it right.

 

[Zack Barresse]

considering that your ranges are right, and the information being looked up is in the same workbook, then how about changing the match type of your formula to exact, by...

=VLOOKUP(12466,Sheet1!$A$5:$D$49219,3,FALSE)

if not in same workbook, this must be altered slightly.

 

[tstoneh]

That didn't seem to work either. there must be something in the text file that wont allow it.

 

[Zack Barresse]

can you post a sample data of sheet1?

 

[tstoneh]

Here you go. It does say that some of the information is in text form in this sheet.

pivot Data.xls
	A	B	C	D
7	000000154	283.5	261.1	8.58%
8	000000155	7229.95	6654.55	8.65%
9	000012452	332.02	317.53	4.56%
10	000012466	1568.63	1475	6.35%
11	000012467	1111.38	1046.03	6.25%
12	000012472	4587.71	4587.71	0.00%
13	000012473	120.96	113.73	6.36%
Sheet1

 

[Ahnold]

Try this formula in F4
=VLOOKUP("*12466",Sheet1!$A$5:$D$49219,3,0)

 

[tstoneh]

What does that do?

 

[Ahnold]

* is a wild card character and will return any value.

 

[tstoneh]

Ok that worked but what if I want to use a formula in place of a typed in number?