Double lookup (maybe) to fill in data in a form

[jkroha]

This is my first time posting so I will try not to screw it up, take it easy on the noob if I do.... First off, using Excel XP:

I am trying to make a form for work that will let a person enter a bag size and descripton from a pull down list and fill in the rest of the data from antoher page.

I am using 31 sheets (one for each day of the month) named '1', '2', etc and a sheet called 'Setup' to hold any shared data. I do this because I wrote a simple macro to create all 31 copies based off a single starter sheet and need the sheets for records.

I have used data verification to use a list function to call for the cells Size and Description.




Sample data:

on Setup sheet"

      A          B                 C                       D         E                   F 
1  Size    Descrpt      Order Code                        Short Size     Short Desc
2   25      Pretzel           115                                 25             Pretzel
3   25      Plain              116                                 50             Plain
4   50      Pretzel           117
5   50      Plain              118

I have the data sorted by size then by description in the A1:C5 data


On a log sheet: I an doing the folliing

In A1 I use data verification on the short size list to let the user pick 25 or 50

In B1 I use data verification on the short description list to let the user pick Pretzel or Plain

In C1 I want to get the order code

So data may look like is sheet '1'

A B C
1 25 Plain 116

With the use only entering A1 and B1, and C1 looking up the data from the Setup page


I am trying to nest index and match commands to do this. My idea was to use two match commands, the first to find the size, then using that ouput to use a match on the new smaller array to now find the description, then using that to index out the data.

I am very new to this and wasy trying this formula:

=index(Setup!$A$2:$C$5,(Match('1'!B1, ('1'!$B$(Match('1'!:A1,Setup!$A$2:$A:5,0)):B5),0),3)

And getting nowhere....

Probably making this overly confusing, any help is appreciated

John K

 

[Aladin Akyurek]

Book5
	A	B	C	D	E	F	G	H	I	J	K	L	M
1								Using SubRange				Using Concat
2	Size	Descrpt	Order Code	Concat		Short Size	Short Desc	Result	Start	End		Result
3	25	Pretzel	115	25#Pretzel		25	Pretzel	115	1	2		115
4	25	Plain	116	25#Plain		50	Plain	118	3	4		118
5	50	Pretzel	117	50#Pretzel
6	50	Plain	118	50#Plain
7
8
Sheet1

Formulas...

1. Using subranges determined by means of MATCH formulas..

H3:

=VLOOKUP(G3,OFFSET($B$3,I3-1,0,J3-I3+1,2),2,0)

I3:

=MATCH(F3,$A$3:$A$6,0)

J3:

=MATCH(F3,$A$3:$A$6,1)

H3:J3 is copied down.

2. Using a multikey approach on concatenated fields...

D3, copied down:

=A3&"#"&B3

L3, copied down:

=INDEX($C$3:$C$6,MATCH(F3&"#"&G3,$D$3:$D$6,0))

 

[jkroha]

Thank you SO MUCH for the reply, it is working perfectly and I am expansding it into a few other sheets I am working on...

I would share the bonus I get with you... if I got a bonus...

Thanks again for your work and help!

John K