VLOOKUP not returning known result(s)

G

goodmachine

Board Regular
Joined
Oct 7, 2009
Messages
70
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).
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Sort by date Sort by votes
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
ExcelMercy said:
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.
Click to expand...

1) Attempting to lookup 8308000601 on the "Kit" worksheet
[TABLE="width: 472"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Item Number[/TD]
[TD]Supplier 1 Active[/TD]
[TD]Supplier 2 Active[/TD]
[TD]Kit #[/TD]
[TD]Kit Part #[/TD]
[/TR]
[TR]
[TD]8308000601[/TD]
[TD]#N/A[/TD]
[TD]8308000601[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[/TR]
</tbody>[/TABLE]

Kit Worksheet

[TABLE="width: 203"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Kit #[/TD]
[TD]Part #[/TD]
[/TR]
[TR]
[TD]4060001003[/TD]
[TD]2801002002[/TD]
[/TR]
[TR]
[TD]4060001003[/TD]
[TD]60042544[/TD]
[/TR]
[TR]
[TD]4060001203[/TD]
[TD]2801002202[/TD]
[/TR]
[TR]
[TD]4060001203[/TD]
[TD]60042544[/TD]
[/TR]
[TR]
[TD]4060001302[/TD]
[TD]2801002301[/TD]
[/TR]
[TR]
[TD]4060001302[/TD]
[TD]60042544[/TD]
[/TR]
[TR]
[TD]8660011001[/TD]
[TD]90258[/TD]
[/TR]
[TR]
[TD]8660011001[/TD]
[TD]8308000601[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
skywriter said:
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.
Click to expand...

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.
 
Upvote 0
Kit Sheet

Excel 2010
AB

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Kit #[/TD]
[TD="align: center"]Part #[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]4060001003[/TD]
[TD="align: center"]2801002002[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]4060001003[/TD]
[TD="align: center"]60042544[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]4060001203[/TD]
[TD="align: center"]2801002202[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]4060001203[/TD]
[TD="align: center"]60042544[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]4060001302[/TD]
[TD="align: center"]2801002301[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]4060001302[/TD]
[TD="align: center"]60042544[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]8660011001[/TD]
[TD="align: center"]90258[/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]8660011001[/TD]
[TD="align: center"]8308000601[/TD]

</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
ABCDE
Item NumberSupplier 1 ActiveSupplier 2 ActiveKit #Kit Part #
830800060183080006018660011001
4060001003
4060001003
4060001203
4060001003
4060001302
4060001003
8660011001

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]#N/A[/TD]

[TD="align: right"]#N/A[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2801002002[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]60042544[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]2801002202[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]60042544[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]2801002301[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]60042544[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]90258[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

</tbody>
LookUp​


If you have other fields & a # to match you can apply it across all your columns.
 
Upvote 0
goodmachine said:
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.
Click to expand...

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
ABC

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]10[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]123[/TD]
[TD="align: right"]FALSE[/TD]

</tbody>
Sheet20​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C10[/TH]
[TD="align: left"]=A10=B10[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
goodmachine said:
1) Attempting to lookup 8308000601 on the "Kit" worksheet
[TABLE="width: 472"]
<TBODY>[TR]
[TD]Item Number
[/TD]
[TD]Supplier 1 Active
[/TD]
[TD]Supplier 2 Active
[/TD]
[TD]Kit #
[/TD]
[TD]Kit Part #
[/TD]
[/TR]
[TR]
[TD]8308000601
[/TD]
[TD]#N/A
[/TD]
[TD]8308000601
[/TD]
[TD]#N/A
[/TD]
[TD]#N/A
[/TD]
[/TR]
</TBODY>[/TABLE]

Kit Worksheet

[TABLE="width: 203"]
<TBODY>[TR]
[TD]Kit #
[/TD]
[TD]Part #
[/TD]
[/TR]
[TR]
[TD]4060001003
[/TD]
[TD]2801002002
[/TD]
[/TR]
[TR]
[TD]4060001003
[/TD]
[TD]60042544
[/TD]
[/TR]
[TR]
[TD]4060001203
[/TD]
[TD]2801002202
[/TD]
[/TR]
[TR]
[TD]4060001203
[/TD]
[TD]60042544
[/TD]
[/TR]
[TR]
[TD]4060001302
[/TD]
[TD]2801002301
[/TD]
[/TR]
[TR]
[TD]4060001302
[/TD]
[TD]60042544
[/TD]
[/TR]
[TR]
[TD]8660011001
[/TD]
[TD]90258
[/TD]
[/TR]
[TR]
[TD]8660011001
[/TD]
[TD]8308000601
[/TD]
[/TR]
</TBODY>[/TABLE]
Click to expand...

What is the formula you tried?
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
If VLOOKUP is @NA check Table 2
Replies
9
Views
737
Joe4
Joe4
O
  • Question Question
Vlookup() and Index(Match()) returning N/A, even though the lookup value exists in the range and is identical to that value within the range.
Replies
6
Views
2K
nemmi69
nemmi69
D
  • Question Question
VLookup returns the result from the row above
Replies
8
Views
1K
Peter_SSs
Peter_SSs
B
  • Solved
VLOOKUP returns #N/A when lookup value is an output of a formula
Replies
3
Views
2K
Peter_SSs
Peter_SSs
5
  • Question Question
VLOOKUP Loop to return multiple rows and columns
Replies
6
Views
1K
Alex Blakenburg
A

Forum statistics

Threads
1,222,095
Messages
6,163,900
Members
451,865
Latest member
dunworthc

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top