VLOOKUP not returning known result(s)

[goodmachine]

Hello,

I'm having difficulty with a VLOOKUP that is not retrieving known results. Hence, I can't trust the data.

I've done a variety of things to clean up the source data (table array)

• Converted cell to a number
• Changed cell format to general
• Removed any extra spaces
• Changed the column position of the table array

None of these returned the desired LOOKUP return.

I also changed my lookup fomula to include, e.g., =VLOOKUP(TEXT(A227,0),Kit!B1:B1729,2,FALSE)

Any other ideas about what I can do? Any other formulas I could try (e.g., index match).

 

[ExcelMercy]

I usually prefer the index/match function. In conjunction with IFERROR, it can accomplish a lot.

If you post some sample data we can help you write it.

 

[Andrew Poulsom]

See if this helps you:

http://www.contextures.com/xlFunctions02.html#trouble

 

[gsistek]

One thought - the table array you have there is a single column wide and you are trying to return from the second column. Make sure the table array is wide enough for the column index number and make sure the lookup value (A227) is in the first column of the table array.

 

[skywriter]

I would start by making sure the data that you are comparing are seen by Excel as being the same.
So for example you are looking up Text(A227,0) and it should find that in Kit!B22 for example, Go to a blank cell and type in the formula
=Text(A227,0)= then select Kit!B22 and hit enter. If you get true there is not a problem, if you get false that means it doesn't see the 2 cells as being equal.

I helped somebody who was trying to match numbers he typed into a column with number he imported. They looked the same, but when using the test above I got a false. I changed the formatting of the imported cells to numbers with about 10 decimals and found out there were some decimals on the imported data causing the problem even though the numbers appeared to be the same, they weren't.

 

[goodmachine]

I usually prefer the index/match function. In conjunction with IFERROR, it can accomplish a lot.

If you post some sample data we can help you write it.

1) Attempting to lookup 8308000601 on the "Kit" worksheet

Item Number	Supplier 1 Active	Supplier 2 Active	Kit #	Kit Part #
8308000601	#N/A	8308000601	#N/A	#N/A

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

Kit Worksheet

Kit #	Part #
4060001003	2801002002
4060001003	60042544
4060001203	2801002202
4060001203	60042544
4060001302	2801002301
4060001302	60042544
8660011001	90258
8660011001	8308000601

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[goodmachine]

I would start by making sure the data that you are comparing are seen by Excel as being the same.
So for example you are looking up Text(A227,0) and it should find that in Kit!B22 for example, Go to a blank cell and type in the formula
=Text(A227,0)= then select Kit!B22 and hit enter. If you get true there is not a problem, if you get false that means it doesn't see the 2 cells as being equal.

I helped somebody who was trying to match numbers he typed into a column with number he imported. They looked the same, but when using the test above I got a false. I changed the formatting of the imported cells to numbers with about 10 decimals and found out there were some decimals on the imported data causing the problem even though the numbers appeared to be the same, they weren't.

Skywriter,

I tried your test and I received a false. There shouldn't be any decimals on these part #s - they are SKU's for print material.

 

[ExcelMercy]

Kit Sheet

Excel 2010

	A	B
1	Kit #	Part #
2	4060001003	2801002002
3	4060001003	60042544
4	4060001203	2801002202
5	4060001203	60042544
6	4060001302	2801002301
7	4060001302	60042544
8	8660011001	90258
9	8660011001	8308000601

<tbody>
</tbody>

Kit​

LookUp Sheet

IN the D2 Cell:
=IFERROR(INDEX(Kit!$A$2:$A$9,MATCH(LookUp!A2,Kit!$B$2:$B$9,0)),"")

Excel 2010

	A	B	C	D	E
1	Item Number	Supplier 1 Active	Supplier 2 Active	Kit #	Kit Part #
2	8308000601	#N/A	8308000601	8660011001	#N/A
3	2801002002			4060001003
4	60042544			4060001003
5	2801002202			4060001203
6	60042544			4060001003
7	2801002301			4060001302
8	60042544			4060001003
9	90258			8660011001

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

LookUp​

If you have other fields & a # to match you can apply it across all your columns.

 

[skywriter]

Skywriter,

I tried your test and I received a false. There shouldn't be any decimals on these part #s - they are SKU's for print material.

If you received a false it means they are not the same data in the eyes of Excel. The decimals I used was an example, it was what was wrong with his data. Your data may be that one is a string to Excel and the other is a number, I don't know, but if you are getting a false there is an issue.

In my example below, the "number" on the left is not a number it is text and the number on the right is a number.

If I had this situation and I tried to lookup the right one using the left it wouldn't work.

Excel 2010

	A	B	C
10	123	123	FALSE

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet20​

Worksheet Formulas Cell Formula C10 =A10=B10 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[Aladin Akyurek]

1) Attempting to lookup 8308000601 on the "Kit" worksheet

Item Number	Supplier 1 Active	Supplier 2 Active	Kit #	Kit Part #
8308000601	#N/A	8308000601	#N/A	#N/A

<TBODY>
</TBODY>

Kit Worksheet

Kit #	Part #
4060001003	2801002002
4060001003	60042544
4060001203	2801002202
4060001203	60042544
4060001302	2801002301
4060001302	60042544
8660011001	90258
8660011001	8308000601

<TBODY>
</TBODY>

What is the formula you tried?