Email:      Pass:    Pass?
Advertisements


Free Excel Forum

Double Vlookup, Is It Possible?

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

i would like to lookup part code in different warehouse (about 10 different whse.)

the current vlookup for part number that i have only return the 1st value (color), but not the 2nd, or 3rd color. (because they have the same part number)
is there away to get around this?
pls see attachment


Similar Excel Video Tutorials

Helpful Excel Macros

Vlookup Function That Searches The Entire Workbook - UDF
- This UDF is a Vlookup function that searches the entire workbook in Excel. The syntax and usage of the function is exac
Determine a Cell's Color with this UDF - Outputs as Text or the Index Number in Excel
- This free Excel UDF allows you to output the color of a cell in text format or as that color's index number. Also note
Highlight Cells with Text or Formulas (non-empty cells)
- This macro will highlight all cells in excel which are not empty. This means that if a cell contains formulas, text, nu
Highlight Every Other Row in a Selection in Excel - Table Formatting
- This free Excel macro will highlight every other row in a selection of cells with a color that is specified within the m
Highlight the Row of the Selected Cell
- This macro will highlight the row and column of the active cell. This will fill the row of the active cell with a solid

Similar Topics







I have a list of 16,000 part numbers with 3,500 unique part numbers. I want a macro that will highlight the rows of all matching part numbers. I have seen examples of this task with a case statement but I would need 3,500 cases in order for this to work. I don't really care what the colors are or if they repeat, as long as two back-to-back part numbers don't have the same color.

For example:

Part ABC Color 1
Part ABC Color 1
Part ABC Color 1
Part DEF Color 2
Part DEF Color 2


I am looking for a double VLOOKUP to search by a part number, look on a seperate spreadsheet for the same part number and return the quantity to ship on different dates....

Please see attached example.

All i am trying to get out of this is have the formula look at a part number, look through all the dates and quantities that this part is suppose to ship (yellow section on example) and return the correct quantity on the correct ship date.

Thank you for your help in advance!


=VLOOKUP(G44,KS!$A$1:$D$26668,3,FALSE)

I'm using that formula in cell F44 of worksheet1. On worksheet KS There are 26000 part numbers in column A, and in Column C (3) there are the corresponding prices. I have the part number entered in G44 of worksheet1, trying to return the price for that part number, but I get a #N/A error. If I copy the part number and go to the other worksheet and paste it into the find box, the part number line comes up with the price, etc. So the part number is correct and on the reference worksheet, but its not returning in the vlookup. Any ideas why this may be?

Thanks in advance!


I am running a vlookup for part #'s at my company. The table that I have may
sometimes contain the same part numbers multiple times. I want my vlookup to
look at all of the same part number and give me a total of the column that
I'm looking in. For example

PART # QUANTITY
15300001 100
15906231 52
15308526 78
15300001 69

Now when I do a lookup on a separate worksheet underneath the part number
15300001 I want my quantity to show 169.

Is this possible?...Please help.

Thanks in advance.

Travis Dahlman



Hi,

I have an ever growing list of stock items that I need to regularly pick from when restocking. Each part has a unique part number eg: M3x8 screw, M3x10 screw. I need the ability to type a part number into a field and have it return the relevant part number with associated details eg; price, from adjoining columns. Have tried vlookup but due to the nature of the list doesnt work for me.

I was thinking of a box to type part number in and a second box that return matching parts from the list?

Any help would be a bonus to me...

Jeff


I looked through several pages of VLOOKUP and couldn't find anything other that doing VBA MULTLOOKUP and was wondering if possible...


I have a part number on one worksheet that looks at another sheet for the same part number to return the yearly usage.
BUT, sometimes there are multiple customers on that sheet that use the same part number. Only the first result is returned.

So right now, it's
=VLOOKUP(C337,forecast!D:CW,98,FALSE)

Yes, I have lots of columns...

Any simple way of having the vlookup return the sum of the one PN from several rows in column 98?


I have a Simple(ish) sheet with three linked columns. The new part number (SAP number), the old part number and the part description. I would like to have it set up that if you knew the old part number OR the new number it would fill in the other two fields.

I have managed to do this (crudely) with the use of a validation list for each part number and VLookups to get the remaining part number and the description, but there must be a more user friendly way of going about it to compensate for mistakes.

For example at the moment if you change the old part number and realise that it should have been the SAP number you changed the Vlookup for the old part number has been replaced with the first value you chose.

There may be a simple way of doing this but i've come to a dead end.

I have attached a copy of the workbook and the above should make more sense along with it.

Thanks in advance,
Mark


How do I create a vlookup function within a worksheet to differ between two values.

For example.

I have a workbook setup with a main worksheet listing all the part names and part numbers I buy.

Separate worksheet will contains Each suppliers pricing for the same parts.

I have named the ranges in supplier worksheets that I would like to use in the vlookup tables.

If I am using the Part number as the main lookup reference, how do I differ between the packaging with my lookup function?

If you open the attachment, you'll notice that the part name and part numbers are the same, but the package size is Bag and Box.

When you run vlookup formula, it never finds the correct Box price from Supplier A and Supplier B's pricing worksheet for the box price.

How do I fix that?


Hi

I have a spreadsheet with thousands of lines and need to easily be able to do a Vlookup and return the total of each part number.

I have tried using the data subtotal option, but I receive the first amount instead of the total.

Part number in row A
count in row B

I need a to return a total for each part number and do a Vlookup to put that amount into another worksheet.


Thanks

Lostinformulas


Hi To everyone!
I have spread sheet that has about 3,000 rows, which all contain a combination of part numbers and parital discriptions for part numbers (in, say col. A.)
Is there a way that I can turn the font a color (doesn't matter color) when there are duplicte part numbers and or partial description for part numbers.
Presently I have to manully go thru each row and turn the font a color when it is a duplicte (I sort first and then do the font coloring) number or partial description for a part number (example, paritial descrip. part number - 3 flute end mill etc..) I get these kinds of projects every once in a while and it would be great to have a formula or code to highlight the duplicates so i can sort on color.

Thanks

Dan


HELP PLEASE!
I use a vlookup formula to bring in changing quantities for parts each week. Several parts have the same part number but for whatever reason a different part name, essentially making it a different part. Because Excel brings in the first true instance for vlookup i end getting the same quantity in several erroneous places.

Unfortunately there is nothing else unique about these line items. Is there a way to tell the vlookup to check for two certain lookup values and only return a quantity if both conditions are met, not just one?

Sorry if it's confusing but the integrity of my inventory is shot because of this and I need a fix.


I've seen some others ask this question, but I think mine is a little simpler.

Column A has a part number
Next to it, in Column B is a component of that part number.

No problem.

However! Sometimes a part number has more than one component.
In this terrible spreadsheet I have to pull the data from
the part number will be listed as many times as needed in Column A with the component
in Column B

A vlookup with find the first time the part number appears in Column A and report what is next to it in Column B

No problem.

I want a formula that will continue on in Column A and return ALL components for that duplicated part number.


In column A, I have our part number for a given product; in col B is our vendor's part number; col C is empty; and col D is again the vendor part number. The reason I have the vendor part number in two columns is because col B has more rows (all the vendors part numbers) than col D (just the ones we use).

What I am trying to do is a vlookup in col C that looks for the col D value in col B, and then returns col A, but am having trouble doing this. The formula I've written in C2 is:

=VLOOKUP(D$2,A$2:B$1254,2,0)

But all it returns in col C is #N/A. I can't figure this out and appreciate any help. Thanks.

Mark


Hi there.
Can anyone please advise how I can use this formula "=IF(ISNA(VLOOKUP(D1,$AK$1:$AK$35501,1,0)),"Not In Both","In Both")" in excel VBA as there are going to be about 60,000 records to be matched?
Or is there any other way in vba?

Code:

D                  E               AK

1  Part # 1   In Both         Part # 3
2  Part # 3   In Both         Part # 4
3  Part # 7   Not In Both     Part # 1
4  Part # 4   In Both



Thanks


Is it possible to do a VLOOKUP with just part of a number? Our part numbers change level frequently. For example:
1234567AA
1234567AB
1234567AC
So, in July the part number in the VLOOKUP would be at the AA level but, by August we would need to compare part number lists with the AB level of the same part number. Will a VLOOKUP find base numbers?


I want to look up part 2 in the table below for a quantity of 6. I want to ignore anything with a unit $ of $0. So the result that I would want for this example is $0.21 (based on the next highest quantity which is 25).
So in summary I want: Part #, quantity closest to requested quantity, then result to be that unit $.


Part Number Quantity Unit $ Date
Part 1 2060 $0.41 07/22/08
Part 1 2060 $0.38 12/17/08
Part 1 3500 $1.24 06/29/08
Part 2 490 $0.21 06/29/08
Part 2 810 $0.21 06/29/08
Part 2 540 $0.21 06/29/08
Part 2 225 $0.21 09/10/08
Part 2 5 $- 11/18/08
Part 2 34 $0.21 07/23/08
Part 2 516 $0.21 09/02/08
Part 2 200 $0.21 09/12/08
Part 2 50 $0.21 02/10/09
Part 2 25 $0.21 03/30/09
Part 2 1931 $0.24 10/20/09
Part 2 100 $0.24 03/31/10
Part 2 760 $0.17 06/29/08
Part 3 40 $14.90 02/11/09
Part 3 20 $14.90 03/30/09
Part 3 40 $14.90 06/05/09
Part 3 20 $14.90 09/14/09
Part 3 20 $14.90 10/19/09
Part 3 20 $14.90 01/04/10
Part 3 10 $59.50 02/15/10

Any help would be appreciated.

Thanks,

Chad


I need a formula to look up a value in column a and a value in column b and return the value that is in column c.

For example:

Part Number Date Unit $
Part 1 12/17/08 $0.68
Part 2 03/31/10 $0.21
Part 3 04/20/10 $26.05
Part 4 09/14/09 $4.31
Part 1 03/10/09 $1,693.59
Part 2 11/04/08 $13.17
Part 3 09/29/09 $6.02
Part 4 02/17/10 $11.17
Part 1 11/09/09 $34.59
Part 2 06/23/09 $34.94
Part 3 01/13/10 $0.11

So if I want Part 3 with a date of 09/29/09, then the result would be $6.02.

Thanks,

Chad


I'm trying to use vlookup and have a column that shows the first two characters of a possible part number. I want to enter the part number in a cell and in the cell next to it have vlookup grab the first two characters of that part number and compare it to the reference. So, I'm trying to use left in my vlookup formula only it's giving me a #n/a error. Here is my vlookup formula:
=VLOOKUP((LEFT(B4,2)),A19:L26,5,FALSE)

If I replace the (LEFT(B4,2)) with the number 25 (the first two digits of the part number in cell B4) I get a value but when I put the (LEFT(B4,2)) back into the formula it doesn't work. Does VLOOKUP not play well with other formulas?


Forgive me if this has been asked and answered before, but I have searched and not seen this particular problem.

I have a table of part and purchase order data - column A is part numbers and columns B-E are purchase order numbers.

Elsewhere I want to do a lookup that, for a given part number on this table, will return the first purchase order number given. (Some part numbers have multiple PO numbers, but I am only interested in the first one that appears in each row.) Ideally I would like to do this without a series of multiple nested IF(ISNA(VLOOKUP functions.

Put another way (I think) in the vlookup syntax of VLOOKUP(lookup value, array, column number, FALSE), I need to find a way to vary the column index number so as to head for the first non-blank value.

I have a sneaking suspicion I should be using a combination of INDEX and MATCH to do this, but I'm not quite sure of that, or how.

Any help is greatly appreciated.


Hi again.

Suppose I have a hundreds of part number like this:
AC1000130

The number 1000 in the above part number represents a sub-group that I need to vlookup.

If the part number is in cell A2, and the name of the table I am searching is "sub_group", how would I perform a vlookup?

Thanks in advance.

Hutch


Hi Guys

Was just wondering if anyone out their would know how to have a vlookup look in two different workbooks. For example if the lookup did not return with a value then to lookup another workbook. The formula I have been working towards looks like this but I am having no luck.

=VLOOKUP(A1,[test2.xls]Sheet1!$A:$B,2,FALSE)=IF(B1<1,VLOOKUP(A1,[test3.xls]Sheet1!$A:$B,2,FALSE)

The first part is the vlookup and the second part is an =if forumula. So what I am trying to do is to do the vlookup and if the value does not return (which would be indicated by a value of 0, then do an =if argument so it does the lookup in another workbook. Does this make sense or am I way of the mark.

Please advice & many thanks!!


I am trying to use VLOOKUP to return results on a spread sheet. I am using Excel on office 2000. I want to type in a part number and have the spread sheet give me all the info for that part number. I have done this with the VLOOKUP formula and everything works fine except...there are some duplicate part numbers. My formula only gives me the info of one part number, I want to be able to have it give me all examples of the same part number. So if there are 3 parts that use the same number, when I type in the number I want info on all three parts. If there is only 1 part with that number then I would only get 1 set of info. Can this be done and if so what is the formula?

Thanks for the help, this is the first time I have tried this.


Hi folks,

I'm trying to return the next unique item in the list of partnumbers.
The count column indicates that there are that many instances of that part number. I then populate down than many to VLOOKUP the value "count" number of times. The formula I am using to get the MFR code compares the pn to the one above it. If it matches, I want it to return the MFR offset by the one above it for that part number.

Now I hope al that made sense.


Source data: (will alwyas be sorted by part number, then by MFR)
Part Number Stock num NAME MFR
BDEGHS555 55544333 RIVET,BLIND 53551
BDEGHS555 55544333 RIVET,BLIND 92215
BDEGHS555 55544333 RIVET,BLIND 98996
BDEGHS555 55544333 RIVET,BLIND C4293

Results:
Count Part Number MFR Stock num NAME
4 BDEGHS555 53551 55544333 RIVET,BLIND
4 BDEGHS555 53551 55544333 RIVET,BLIND
4 BDEGHS555 53551 55544333 RIVET,BLIND
4 BDEGHS555 53551 55544333 RIVET,BLIND

I want the first vlookup part of the formula to return the NEXT MFR code in the source data.

Code:

=IF(B18<>"",IF(B18=B17,VLOOKUP(TRIM(B18),'Part Numbers'!A:E,4,FALSE),VLOOKUP(TRIM(B18),'Part Numbers'!A:E,4,FALSE)),"")





I need to write a vlookup formula. That isn't the problem. The problem is on tab 1 I will be entering a part number. The number I enter will be a generic part number (example 123456) and it will look it up on tab 2 where the vlookup table is. The part numbers in the table will be actual part numbers (example 123456*R1). The two part numbers will always have the same 6 numbers on both tabs but on tab 2 the part numbers will have a *revision number. How can I do a vlookup even though the numbers are identical. I have been told wildcards??? I dont know how to add those to formula though if that is the way. Thanks in advance.


Hi

I have a unique part number with various order numbers. I need to return the price of the specific part by first looking at the order number reference from a list that contains all orders placed. What function can I use because it seems that if I use VLOOKUP everytime it returns the first part number's price on the list...meaning it looks like this:

B001234(part num) $12.00 1101657-011211-CSI(order num)
B001234(part num) $24.60 1101503-101910-CSI(order num)
B001234(part num) $66.01 GSSV014479-AP1012002A(order num)
B001234(part num) $12.00 GSSV013920-DL0277001J(order num)

the difficult part is that the order num is not on the same line as the part num.
If I want to return the price for order number GSSV014479-AP1012002A for part num B001234 it does not return $66.01 but the first line $12.00.

I have been battling with this for the whole day today and is looking like

I sincerely hope somebody out there can assist