dynamic vlookup

[MrMmnico]

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?

 

[Bernie Deitrick]

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

HTH,
Bernie
MS Excel MVP

 

[MrMmnico]

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]

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

 

[Max]

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
---

 

[MrMmnico]

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

 

[Max]

Welcome, glad it helped.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,700 Files:359 Subscribers:55
xdemechanik