Vlook up with match formula

[jedilefty]

Hi, I'm new to this forum.
I am trying to create Part and Price data base, where entering in a particular part number and quantity pulls up the serial number and price depending on quantity ordered. I was able to do a VLOOK UP [formula used is "=VLOOKUP(D2,$A$7:$B$61,2,FALSE)"] to find the serial numbers, however the part I'm struggling with is how to pull up the price. The pricing would be from columns L through T, with headers in row 7. Can anyone help please? Not sure how to upload an image of my spreadsheet for a better visual.

 

[AhoyNC]

Re: Vlook up with match formula help

See if this example helps.

One way to copy part of your spreadsheet is to put a border around the cells you want to copy and then use copy paste.

Excel Workbook
	A	B	C	D	E	F
1	Serial # / Qty	1	10	15	20	50
2	Ser1	35	30	25	15	10
3	Ser2	52	45	40	35	26
4	Ser3	15	12	10	8	5
5	Ser4	45	40	35	30	25
6
7
8	Serial #	Ser2
9	Qty:	18
10	Price	40
Sheet

 

[jedilefty]

Re: Vlook up with match formula help

AhoyNC:

I need the price based on the prices stated in columns L through T to populate in cell D5. The formula provided wasn't exactly what I was looking for. I figured out how to show what my spreadsheet looks like for visual on what i'm trying to explain. So once a qty is entered into D4, based on that quantity for the part# entered into D2, pulls up the pricing. Hope this makes more sense.



[TABLE="class: grid, width: 1463"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col span="10"></colgroup><tbody>[TR]
[TD] [/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[TD]S[/TD]
[TD]T[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]PART #[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]Serial #[/TD]
[TD] [/TD]
[TD="colspan: 7"]FORMULA USED FOR SERIAL# IS {=VLOOKUP(D2,$A$7:$B$61,2,FALSE)}[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD]Qty[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD]Price[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 9"]PRICE PER PIECE[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]PART#[/TD]
[TD]SERIAL#[/TD]
[TD]ALTERNATE P/N[/TD]
[TD]REVIEW DATE[/TD]
[TD]LEAD TIME[/TD]
[TD]LINK[/TD]
[TD]INVENTORY LVL[/TD]
[TD] [/TD]
[TD]FA CHARGE[/TD]
[TD]SETUP[/TD]
[TD]MIN CHARG[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]10[/TD]
[TD]25[/TD]
[TD]50[/TD]
[TD]100[/TD]
[TD]250[/TD]
[TD]500[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD="align: right"]1[/TD]
[TD]A[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] $ 1,000.00[/TD]
[TD] $ 500.00[/TD]
[TD] $ 350.00[/TD]
[TD] $ 175.00[/TD]
[TD] $ 59.00[/TD]
[TD] $ 40.00[/TD]
[TD] $ 35.00[/TD]
[TD] $ 35.00[/TD]
[TD] $ 35.00[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD="align: right"]2[/TD]
[TD]B[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] $ 980.00[/TD]
[TD] $ 350.00[/TD]
[TD] $ 250.00[/TD]
[TD] $ 100.00[/TD]
[TD] $ 35.00[/TD]
[TD] $ 25.00[/TD]
[TD] $ 15.00[/TD]
[TD] $ 15.00[/TD]
[TD] $ 15.00[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD="align: right"]3[/TD]
[TD]C[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[AhoyNC]

Re: Vlook up with match formula help

Try:

Excel Workbook
	A	B	C	D	E	K	L	M	N	O	P	Q	R	S	T
1
2			PART #	2
3			Serial #	B
4			Qty	206
5			Price	25
6							PRICE PER PIECE
7	PART#	SERIAL#	ALTERNATE P/N	REVIEW DATE	LEAD TIME	MIN CHARG	1	5	10	25	50	100	250	500	1000
8	1	A					$1,000.00	$500.00	$350.00	$175.00	$59.00	$40.00	$35.00	$35.00	$35.00
9	2	B					$980.00	$350.00	$250.00	$100.00	$35.00	$25.00	$15.00	$15.00	$15.00
10	3	C
Sheet

 

[jedilefty]

Re: Vlook up with match formula help

I copied the formula you provided, and I don't what I might have done wrong. Whenever I type in a quantity in cell D4, it only pulls up the values in column L, no matter what quantity I enter. I see it worked for you in your example, so I'm confused as to why the exact formula will not work on my end. Any ideas?

Formula entered in cell D5 is =INDEX($L$8:$T$9,MATCH($D$3,$B$8:$B$60,0),MATCH($D$4,$L$7:$T$7,1))

[TABLE="class: grid, width: 1511"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]TIER 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]PART NUMBER[/TD]
[TD]AB[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]NSN[/TD]
[TD]123[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]QUANTITY[/TD]
[TD]25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]PART PRICE[/TD]
[TD]880[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 9"]TIER 1[/TD]
[/TR]
[TR]
[TD]PART NUMBER[/TD]
[TD]NSN[/TD]
[TD]ALTERNATE P/N[/TD]
[TD]REVIEW DATE[/TD]
[TD]LEAD TIME WEEKS[/TD]
[TD]COST SHEET LINK[/TD]
[TD]INVENTORY LEVEL[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]10[/TD]
[TD]25[/TD]
[TD]50[/TD]
[TD]100[/TD]
[TD]250[/TD]
[TD]500[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]123[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]$880.00[/TD]
[TD]$450.00[/TD]
[TD]$250.00[/TD]
[TD]$150.00[/TD]
[TD]$50.00[/TD]
[TD]$35.00[/TD]
[TD]$30.00[/TD]
[TD]$31.00[/TD]
[TD]$32.00[/TD]
[/TR]
[TR]
[TD]AC[/TD]
[TD]124[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]$775.00[/TD]
[TD]$350.00[/TD]
[TD]$150.00[/TD]
[TD]$125.00[/TD]
[TD]$45.00[/TD]
[TD]$30.00[/TD]
[TD]$25.00[/TD]
[TD]$28.00[/TD]
[TD]$30.00[/TD]
[/TR]
[TR]
[TD]AD[/TD]
[TD]125[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]$675.00[/TD]
[TD]$250.00[/TD]
[TD]$50.00[/TD]
[TD]$100.00[/TD]
[TD]$40.00[/TD]
[TD]$25.00[/TD]
[TD]$20.00[/TD]
[TD]$26.00[/TD]
[TD]$28.00[/TD]
[/TR]
[TR]
[TD]AE[/TD]
[TD]126[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]$550.00[/TD]
[TD]$150.00[/TD]
[TD]$25.00[/TD]
[TD]$75.00[/TD]
[TD]$35.00[/TD]
[TD]$20.00[/TD]
[TD]$15.00[/TD]
[TD]$20.00[/TD]
[TD]$22.00[/TD]
[/TR]
</tbody>[/TABLE]

 

[jedilefty]

Re: Vlook up with match formula help

Ok so I copied the formula you provided and it's only pulling up the values in column L. I see it worked in your example provided, so I'm confused as to why it's not working for me. Any ideas?

formula entered into cell D5 is =INDEX($L$8:$T$9,MATCH($D$3,$B$8:$B$60,0),MATCH($D$4,$L$7:$T$7,1))




[TABLE="class: grid, width: 1511"]
<colgroup><col><col><col><col><col><col span="3"><col span="9"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]TIER 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]PART NUMBER[/TD]
[TD]AB[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]NSN[/TD]
[TD]123[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]QUANTITY[/TD]
[TD]25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]PART PRICE[/TD]
[TD]880[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 9"]TIER 1[/TD]
[/TR]
[TR]
[TD]PART NUMBER[/TD]
[TD]NSN[/TD]
[TD]ALTERNATE P/N[/TD]
[TD]REVIEW DATE[/TD]
[TD]LEAD TIME WEEKS[/TD]
[TD]COST SHEET LINK[/TD]
[TD]INVENTORY LEVEL[/TD]
[TD] [/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]10[/TD]
[TD]25[/TD]
[TD]50[/TD]
[TD]100[/TD]
[TD]250[/TD]
[TD]500[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]AB[/TD]
[TD]123[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]$880.00[/TD]
[TD]$450.00[/TD]
[TD]$250.00[/TD]
[TD]$150.00[/TD]
[TD]$50.00[/TD]
[TD]$35.00[/TD]
[TD]$30.00[/TD]
[TD]$31.00[/TD]
[TD]$32.00[/TD]
[/TR]
[TR]
[TD]AC[/TD]
[TD]124[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]$775.00[/TD]
[TD]$350.00[/TD]
[TD]$150.00[/TD]
[TD]$125.00[/TD]
[TD]$45.00[/TD]
[TD]$30.00[/TD]
[TD]$25.00[/TD]
[TD]$28.00[/TD]
[TD]$30.00[/TD]
[/TR]
[TR]
[TD]AD[/TD]
[TD]125[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]$675.00[/TD]
[TD]$250.00[/TD]
[TD]$50.00[/TD]
[TD]$100.00[/TD]
[TD]$40.00[/TD]
[TD]$25.00[/TD]
[TD]$20.00[/TD]
[TD]$26.00[/TD]
[TD]$28.00[/TD]
[/TR]
[TR]
[TD]AE[/TD]
[TD]126[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]$550.00[/TD]
[TD]$150.00[/TD]
[TD]$25.00[/TD]
[TD]$75.00[/TD]
[TD]$35.00[/TD]
[TD]$20.00[/TD]
[TD]$15.00[/TD]
[TD]$20.00[/TD]
[TD]$22.00[/TD]
[/TR]
</tbody>[/TABLE]

 

[AhoyNC]

Re: Vlook up with match formula help

Your formula worked for me in the example you posted see formula in red.
However if your part numbers go down to row 60 then you need to increase the INDEX range as in the formula in yellow.

Excel Workbook
	A	B	C	D	E	H	K	L	M	N	O	P	Q	R	S	T
1			1. TIER 1
2			PART NUMBER	AB
3			NSN	123
4			QUANTITY	25
5			PART PRICE	150	150
6								TIER 1
7	PART NUMBER	NSN	ALTERNATE P/N	REVIEW DATE	LEAD TIME WEEKS			1	5	10	25	50	100	250	500	1000
8	AB	123						$880.00	$450.00	$250.00	$150.00	$50.00	$35.00	$30.00	$31.00	$32.00
9	AC	124						$775.00	$350.00	$150.00	$125.00	$45.00	$30.00	$25.00	$28.00	$30.00
10	AD	125						$675.00	$250.00	$50.00	$100.00	$40.00	$25.00	$20.00	$26.00	$28.00
11	AE	126						$550.00	$150.00	$25.00	$75.00	$35.00	$20.00	$15.00	$20.00	$22.00
Sheet

 

[jedilefty]

So I think I figured out what was wrong. It had something to do with the formatting of cells L7 - T7. I fixed it and now everything works perfectly. Thank you so much!!! You are a life saver!!

 

[AhoyNC]

You're welcome. Glad you got it to work. I assumed (not always the best thing to do) what your cells L7 t0 T7 were numeric which they need to be for the formula to work.