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).
 
Sort by date Sort by votes

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
goodmachine said:
This is a formula that I tried.

=VLOOKUP(TEXT(A227,0),Kit!B1:B1729,2,FALSE)
Click to expand...

1. This formula specifies a 2-colum table, while Kit!B1:B1729 consists of just 1 column.

2. Your earlier post displays a 2 column table with Kit # and Part # as headers, in that order:

[TABLE="width: 160"]
<COLGROUP><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3413" width=96><COL style="WIDTH: 88pt; mso-width-source: userset; mso-width-alt: 4181" width=118><TBODY>[TR]
[TD="class: xl63, width: 96, bgcolor: transparent"]Kit #[/TD]
[TD="class: xl63, width: 118, bgcolor: transparent"]Part #[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]4060001003[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]2801002002[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]4060001003[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]60042544[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]4060001203[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]2801002202[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]4060001203[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]60042544[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]4060001302[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]2801002301[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]4060001302[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]60042544[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]8660011001[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]90258[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent, align: right"]8660011001[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]8308000601[/TD]
[/TR]
</TBODY>[/TABLE]

Which column in this table corresponds to Kit1!B1:B1729?
 
Upvote 0
Hello Aladin,

The column corresponds to the second one. I should have clarified in my earlier statements that 8308000601 could be found in the Kit # and Part # columns. But in the example I provided, that value would be found in the part # column.

1) Attempting to lookup 8308000601 on the "Kit" worksheet
[TABLE="class: cms_table, width: 472"]
<tbody>[TR]
[TD]Item Number[/TD]
[TD]Supplier 1 Active[/TD]
[TD]Supplier 2Active[/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="class: cms_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]

 
Upvote 0
goodmachine said:
Hello Aladin,

The column corresponds to the second one. I should have clarified in my earlier statements that 8308000601 could be found in the Kit # and Part # columns. But in the example I provided, that value would be found in the part # column.

1) Attempting to lookup 8308000601 on the "Kit" worksheet
[TABLE="class: cms_table, width: 472"]
<TBODY>[TR]
[TD]Item Number
[/TD]
[TD]Supplier 1 Active
[/TD]
[TD]Supplier 2Active
[/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]
...
Click to expand...

Ok. The Kit worksheet is now clear.

In which sheet is he above exhibit located?

What are the expected values instead of those #N/A unless these #N/A values what you want to see?
 
Upvote 0
Aladin Akyurek said:
Ok. The Kit worksheet is now clear.

In which sheet is he above exhibit located?

What are the expected values instead of those #N/A unless these #N/A values what you want to see?
Click to expand...


The sheet above is named "Active 9.1.14" or Active for short in the context of this post. The Active sheet contains all of the the active SKU's in our inventory and compares what is active on the vendor side.
  1. The Active worksheet does a lookup against what is active on the vendor's active inventory (Supplier 2) and the Kit report.
  2. Logically, if the SKU is active on the supplier's active inventory report and on the kit report, the lookup should return that value in the Kit # or Part #.

Unfortunately, that is not happening.
 
Upvote 0
goodmachine said:
The sheet above is named "Active 9.1.14" or Active for short in the context of this post. The Active sheet contains all of the the active SKU's in our inventory and compares what is active on the vendor side.
  1. The Active worksheet does a lookup against what is active on the vendor's active inventory (Supplier 2) and the Kit report.
  2. Logically, if the SKU is active on the supplier's active inventory report and on the kit report, the lookup should return that value in the Kit # or Part #.

Unfortunately, that is not happening.
Click to expand...

All this must be too obvious to you...

Given this Kit sample:

[TABLE="width: 164"]
<COLGROUP><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3527" width=99><COL style="WIDTH: 90pt; mso-width-source: userset; mso-width-alt: 4266" width=120><TBODY>[TR]
[TD="class: xl65, width: 99, bgcolor: white"]Kit #[/TD]
[TD="class: xl65, width: 120, bgcolor: white"]Part #[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 99, bgcolor: white"]4060001003[/TD]
[TD="class: xl66, width: 120, bgcolor: white"]2801002002[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 99, bgcolor: white"]4060001003[/TD]
[TD="class: xl66, width: 120, bgcolor: white"]60042544[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 99, bgcolor: white"]4060001203[/TD]
[TD="class: xl66, width: 120, bgcolor: white"]2801002202[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 99, bgcolor: white"]4060001203[/TD]
[TD="class: xl66, width: 120, bgcolor: white"]60042544[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 99, bgcolor: white"]4060001302[/TD]
[TD="class: xl66, width: 120, bgcolor: white"]2801002301[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 99, bgcolor: white"]4060001302[/TD]
[TD="class: xl66, width: 120, bgcolor: white"]60042544[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 99, bgcolor: white"]8660011001[/TD]
[TD="class: xl66, width: 120, bgcolor: white"]90258[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 99, bgcolor: white"]8660011001[/TD]
[TD="class: xl67, width: 120, bgcolor: white"]8308000601[/TD]
[/TR]
</TBODY>[/TABLE]

what must be the results in the blue cells on Active 9.1.14 that is below?

[TABLE="width: 507"]
<COLGROUP><COL style="WIDTH: 98pt; mso-width-source: userset; mso-width-alt: 4664" width=131><COL style="WIDTH: 127pt; mso-width-source: userset; mso-width-alt: 6001" width=169><COL style="WIDTH: 127pt; mso-width-source: userset; mso-width-alt: 6030" width=170><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3214" width=90><COL style="WIDTH: 87pt; mso-width-source: userset; mso-width-alt: 4124" width=116><TBODY>[TR]
[TD="class: xl65, width: 131, bgcolor: white"]Item Number[/TD]
[TD="class: xl65, width: 169, bgcolor: white"]Supplier 1 Active[/TD]
[TD="class: xl65, width: 170, bgcolor: white"]Supplier 2 Active[/TD]
[TD="class: xl65, width: 90, bgcolor: white"]Kit #[/TD]
[TD="class: xl65, width: 116, bgcolor: white"]Kit Part #[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 131, bgcolor: white"]8308000601[/TD]
[TD="class: xl67, width: 169, bgcolor: #b7dee8"] [/TD]
[TD="class: xl66, width: 170, bgcolor: white"]8308000601[/TD]
[TD="class: xl67, width: 90, bgcolor: #b7dee8"] [/TD]
[TD="class: xl67, width: 116, bgcolor: #b7dee8"] [/TD]
[/TR]
</TBODY>[/TABLE]
 
Upvote 0
Aladin Akyurek said:
All this must be too obvious to you...

Given this Kit sample:

[TABLE="width: 164"]
<tbody>[TR]
[TD="class: xl65, width: 99, bgcolor: white"]Kit #[/TD]
[TD="class: xl65, width: 120, bgcolor: white"]Part #[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 99, bgcolor: white"]4060001003[/TD]
[TD="class: xl66, width: 120, bgcolor: white"]2801002002[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 99, bgcolor: white"]4060001003[/TD]
[TD="class: xl66, width: 120, bgcolor: white"]60042544[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 99, bgcolor: white"]4060001203[/TD]
[TD="class: xl66, width: 120, bgcolor: white"]2801002202[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 99, bgcolor: white"]4060001203[/TD]
[TD="class: xl66, width: 120, bgcolor: white"]60042544[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 99, bgcolor: white"]4060001302[/TD]
[TD="class: xl66, width: 120, bgcolor: white"]2801002301[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 99, bgcolor: white"]4060001302[/TD]
[TD="class: xl66, width: 120, bgcolor: white"]60042544[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 99, bgcolor: white"]8660011001[/TD]
[TD="class: xl66, width: 120, bgcolor: white"]90258[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 99, bgcolor: white"]8660011001[/TD]
[TD="class: xl67, width: 120, bgcolor: white"]8308000601[/TD]
[/TR]
</tbody>[/TABLE]

what must be the results in the blue cells on Active 9.1.14 that is below?

I would expect to see only the Kit Part # returned in this example. Supplier 1 is not relevant in this case. Kit # would not be returned because 8308000601 is only a part #.

[TABLE="width: 507"]
<tbody>[TR]
[TD="class: xl65, width: 131, bgcolor: white"]Item Number[/TD]
[TD="class: xl65, width: 169, bgcolor: white"]Supplier 1 Active[/TD]
[TD="class: xl65, width: 170, bgcolor: white"]Supplier 2 Active[/TD]
[TD="class: xl65, width: 90, bgcolor: white"]Kit #[/TD]
[TD="class: xl65, width: 116, bgcolor: white"]Kit Part #[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 131, bgcolor: white"]8308000601[/TD]
[TD="class: xl67, width: 169, bgcolor: #b7dee8"](IGNORE)[/TD]
[TD="class: xl66, width: 170, bgcolor: white"]8308000601[/TD]
[TD="class: xl67, width: 90, bgcolor: #b7dee8"]#N/A[/TD]
[TD="class: xl67, width: 116, bgcolor: #b7dee8"]8308000601[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...


I would expect to see only the Kit Part # returned in this example. Supplier 1 is not relevant in this case. Kit # would not be returned because 8308000601 is only a part #.

[TABLE="width: 507"]
<tbody>[TR]
[TD="class: xl65, width: 131"]Item Number[/TD]
[TD="class: xl65, width: 169"]Supplier 1 Active[/TD]
[TD="class: xl65, width: 170"]Supplier 2 Active[/TD]
[TD="class: xl65, width: 90"]Kit #[/TD]
[TD="class: xl65, width: 116"]Kit Part #[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 131"]8308000601[/TD]
[TD="class: xl67, width: 169, bgcolor: #B7DEE8"](IGNORE)[/TD]
[TD="class: xl66, width: 170"]8308000601[/TD]
[TD="class: xl67, width: 90, bgcolor: #B7DEE8"]#N/A[/TD]
[TD="class: xl67, width: 116, bgcolor: #B7DEE8"]8308000601

[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
goodmachine said:
I would expect to see only the Kit Part # returned in this example. Supplier 1 is not relevant in this case. Kit # would not be returned because 8308000601 is only a part #.

[TABLE="width: 507"]
<TBODY>[TR]
[TD="class: xl65, width: 131"]Item Number
[/TD]
[TD="class: xl65, width: 169"]Supplier 1 Active
[/TD]
[TD="class: xl65, width: 170"]Supplier 2 Active
[/TD]
[TD="class: xl65, width: 90"]Kit #
[/TD]
[TD="class: xl65, width: 116"]Kit Part #
[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 131"]8308000601
[/TD]
[TD="class: xl67, width: 169, bgcolor: #b7dee8"](IGNORE)
[/TD]
[TD="class: xl66, width: 170"]8308000601
[/TD]
[TD="class: xl67, width: 90, bgcolor: #b7dee8"]#N/A
[/TD]
[TD="class: xl67, width: 116, bgcolor: #b7dee8"]8308000601


[/TD]
[/TR]
</TBODY>[/TABLE]
Click to expand...

D2, copied to E2...

=INDEX(Kit!A:A,MATCH($A2,Kit!A:A,0))

See the wb with this formula:
https://dl.dropboxusercontent.com/u/65698317/goodmachine%20Index%20Match.xlsx
 
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
804
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
P
  • Question Question
TypeScript (or other solutions) for modifying given list of cells (and similar problems)
Replies
0
Views
284
Pingaware
P
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

Forum statistics

Threads
1,224,811
Messages
6,181,082
Members
453,021
Latest member
Justyna P

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