dynamic vlookup

Hi Bernie,
Let me try to understand.
My Columns containing the list is in Col O
eg.
Sheet2
__ a | b | d | 0 |
1 10 |_|_|_|Hard|
2 10 |_|_|_|Soft|
3 10 |_|_|_|Cold|
3 20 |_|_|_|Soft|

and I need it to be.
Sheet1
__ a | b | d | 0 |
1 __ |Hard|Soft|Cold|
2 10 |X|X|X|_|
3 20 |_|X|_|_|


=INDEX(Sheet1!$A$1:$E$4,MATCH(B1,Sheet1!$1:$1,FALSE),MATCH(A1,Sheet1!$O:$O,FALSE))





"Bernie Deitrick" wrote:

> =INDEX(Sheet1!$A$1:$E$4,MATCH(B1,Sheet1!$1:$1,FALSE),MATCH(A1,Sheet1!$A:$A,FALSE))
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "MrMmnico" <MrMmnico.TakeThisOut@discussions.microsoft.com> wrote in message
> news:D0580070-7466-41A7-9DD0-FBC6BB566162@microsoft.com...
> >I am trying to cross reference all occurrences of two sets of values.
> >
> > This is what I need.
> > Sheet 1
> > a b c d e
> > 1 No. Hard Soft Hot Cold
> > 2 10 x x
> > 3 20 x x
> > 4 30 x x x x
> >
> >
> > This is where the data lives
> > Sheet 2
> > a b c d e
> > 1 No. Type
> > 2 30 Hard
> > 3 30 Soft
> > 4 30 Hot
> > 5 40 Soft
> > 6 50 Soft
> > 7 50 Cold
> >
> >
> > What is my best way to accommodate a lookup?
> > index? Offset?
> >
>
>
>

=INDEX(Sheet1!$A$1:$E$4,MATCH(O1,Sheet1!$1:$1,FALSE),MATCH(A1,Sheet1!$A:$A,FALSE))

--
HTH,
Bernie
MS Excel MVP


"MrMmnico" <MrMmnico RemoveThis @discussions.microsoft.com> wrote in message
news:188587F0-B06E-4F27-80BD-D64B427A847E@microsoft.com...
> Hi Bernie,
> Let me try to understand.
> My Columns containing the list is in Col O
> eg.
> Sheet2
> __ a | b | d | 0 |
> 1 10 |_|_|_|Hard|
> 2 10 |_|_|_|Soft|
> 3 10 |_|_|_|Cold|
> 3 20 |_|_|_|Soft|
>
> and I need it to be.
> Sheet1
> __ a | b | d | 0 |
> 1 __ |Hard|Soft|Cold|
> 2 10 |X|X|X|_|
> 3 20 |_|X|_|_|
>
>
> =INDEX(Sheet1!$A$1:$E$4,MATCH(B1,Sheet1!$1:$1,FALSE),MATCH(A1,Sheet1!$O:$O,FALSE))
>
>
>
>
>
> "Bernie Deitrick" wrote:
>
>> =INDEX(Sheet1!$A$1:$E$4,MATCH(B1,Sheet1!$1:$1,FALSE),MATCH(A1,Sheet1!$A:$A,FALSE))
>>
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>>
>> "MrMmnico" <MrMmnico RemoveThis @discussions.microsoft.com> wrote in message
>> news:D0580070-7466-41A7-9DD0-FBC6BB566162@microsoft.com...
>> >I am trying to cross reference all occurrences of two sets of values.
>> >
>> > This is what I need.
>> > Sheet 1
>> > a b c d e
>> > 1 No. Hard Soft Hot Cold
>> > 2 10 x x
>> > 3 20 x x
>> > 4 30 x x x x
>> >
>> >
>> > This is where the data lives
>> > Sheet 2
>> > a b c d e
>> > 1 No. Type
>> > 2 30 Hard
>> > 3 30 Soft
>> > 4 30 Hot
>> > 5 40 Soft
>> > 6 50 Soft
>> > 7 50 Cold
>> >
>> >
>> > What is my best way to accommodate a lookup?
>> > index? Offset?
>> >
>>
>>
>>

How about a pivot table? Takes only a couple of seconds to set it up, and
you'd find that the results very closely fit what you're trying to achieve

Some easy steps to lead you in (in xl2003)

Your col headers in A1:B1 (in Sheet2) are: No., Type
Select any cell within the table, click Data > Pivot Table ..
Click Next > Next

In step 3 of the wiz., click Layout, then:
Drag n drop No. in ROW area
Drag n drop Type in COLUMN area
Drag n drop Type in DATA area (it'll appear as Count of Type)
Click OK > Finish

Hop over to the pivot sheet (just to the left)
where you'd find the desired results

You can simply select and move the col headers, eg: Cold, Hot, etc
left or right to rearrange the col order to taste
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,700 Files:359 Subscribers:55
xdemechanik
---

Without Overcomplecating things the Pivot Table did the trick and it was quick.
Thanks to everyone and the Index also worked great.

"Max" wrote:

> How about a pivot table? Takes only a couple of seconds to set it up, and
> you'd find that the results very closely fit what you're trying to achieve
>
> Some easy steps to lead you in (in xl2003)
>
> Your col headers in A1:B1 (in Sheet2) are: No., Type
> Select any cell within the table, click Data > Pivot Table ..
> Click Next > Next
>
> In step 3 of the wiz., click Layout, then:
> Drag n drop No. in ROW area
> Drag n drop Type in COLUMN area
> Drag n drop Type in DATA area (it'll appear as Count of Type)
> Click OK > Finish
>
> Hop over to the pivot sheet (just to the left)
> where you'd find the desired results
>
> You can simply select and move the col headers, eg: Cold, Hot, etc
> left or right to rearrange the col order to taste
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> Downloads:17,700 Files:359 Subscribers:55
> xdemechanik
> ---

Welcome, glad it helped.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,700 Files:359 Subscribers:55
xdemechanik
---
"MrMmnico" <MrMmnico RemoveThis @discussions.microsoft.com> wrote in message
news:B03B09F5-BD7E-4F2E-A67B-8B26DE68893F@microsoft.com...
> Without Overcomplicating things the Pivot Table did the trick and it was
> quick.
> Thanks to everyone and the Index also worked great.