excel lookup 1

[stpmtp]

I have a problem

I have an excel document that has part and Lenght. In book1
Part# Length
A B
210 3-1/2"
210-A 3-1/2"
211 3-19/64"
220 4"

And in book2 I have

Part# BreakQty Price
A B C
210 1 0.804
210 500 0.501
210 1000 0.401

Is there a way that I can do

Part Lenght BreakQty1 Price1 BreakQty2 Price2 BreaQty3 pr3
A B C D E F G H
210 3-1/2" 1 1.25 500 1 1000 .75


any help is appreciated

Thank you

 

[firefytr]

Hmm, well your prices don't match. But assuming that your results matched the data sets, you would need to add an extra column to Book2 (I'll assume column D - also assuming data starts on row 2 with a 1 line header) with this formula in D2 and copied down ...

=A2&CHAR(11)&COUNTIF($A$2:A2,A2)

Then in your Book3 (I'm assuming, wherever you want your results), enter these formulas ...

A2:
Enter Part # manually

B2:
=VLOOKUP(A2,[Book1.xls]Sheet1!$A:$B,2,0)

C2:
=VLOOKUP($A2,[Book2.xls]Sheet2!$A:$C,2,0)

D2:
=VLOOKUP($A2,[Book2.xls]Sheet2!$A:$C,3,0)

E2:
=INDEX([Book2.xls]Sheet2!$A:$D,MATCH($A2&CHAR(11)&2,[Book2.xls]Sheet2!$D:$D,0),2)

F2:
=INDEX([Book2.xls]Sheet2!$A:$D,MATCH($A2&CHAR(11)&2,[Book2.xls]Sheet2!$D:$D,0),3)

G2:
=INDEX([Book2.xls]Sheet2!$A:$D,MATCH($A2&CHAR(11)&3,[Book2.xls]Sheet2!$D:$D,0),2)

H2:
=INDEX([Book2.xls]Sheet2!$A:$D,MATCH($A2&CHAR(11)&3,[Book2.xls]Sheet2!$D:$D,0),3)


This gives me the results of ...

Part Length BreakQty1 Price1 BreakQty2 Price2 BreaQty3 Price3
210 3-1/2" 1 0.804 500 0.501 1000 0.401

HTH

-----------
Regards,
Zack Barresse

 

[SkipVought]

Hi,

Your example is not clear and consistent.

I named the ranges on book 2 according to the column headings.
[tt]
C2: =INDEX(Book2!BreakQty,MATCH($A2,Book2!Part,0)+MOD(INT((COLUMN()-3)/2),3),1)
D2: =INDEX(Book2!Price,MATCH($A2,Book2!Part,0)+MOD(INT((COLUMN()-3)/2),3),1)
[/tt]
I copied the formulas in C2 & D2 across and got as a result...
[tt]
1 0.804 500 0.501 1000 0.401
[/tt]


Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[stpmtp]

Ahhhh Great!!!!!!!! A million Thanks to you Mr firefytr

you are a gentleman and a scholar

 

[SkipVought]

in fact a single formula that uses your headings as posted...
[tt]
=INDEX(INDIRECT("Book2!"&LEFT(C$1,LEN(C$1)-1)),MATCH($A2,Book2!Part,0)+MOD(INT((COLUMN()-3)/2),3),1)
[/tt]


Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[firefytr]

I don't know about a single formula.. I detest the INDIRECT function looking at other workbooks, they must always be open. There is the INDIRECT.EXT function provided by Laurent Longre in the Morefunc.xll addin, but you only get a single use out of it with a closed workbook and then it's as good as it's predecessor.

-----------
Regards,
Zack Barresse

 

[SkipVought]

I rarely use links to external workbooks. I DETEST external links to begin with. I'd be using a query, more than likely.

But if I can use a single formula, I consider myself WAY AHEAD, IMHO, when it comes to maintaining a worksheet rather than having half a dozen different formulae to mess around with.

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[firefytr]

I guess that's where we differ.

-----------
Regards,
Zack Barresse