Using a formula to look up multipul values based on one other value.

[Browneh89]

Hey,

I hope I can explain this correctly. I'm trying to write a formula which looks at a value and uses it to pull up 2 different lines of criteria..

So in the first table it would look at the number in the area box and compare it to the second table.

it would then pull the code for requirement 1 and 2 in the lower rows in the second table.

I can't use XL2BB to better explain because I am at work!

Thank you for any help in advance!

 

[Fluff]

How about

+Fluff v2.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1
2	Area	6112						Code	Requirement 1	Requirement 2	Area
3								A			6128
4								B			6316
5	Requirement 1	D	E					C		box	6112
6	Requirement 2	C						D	Wrap		6112
7								E	wap		6112
8								F			6350
9
Work

Cell Formulas
Range	Formula
B5:D5	B5	=IFERROR(INDEX($H$3:$H$8,AGGREGATE(15,6,(ROW($H$3:$H$8)-ROW($H$3)+1)/($K$3:$K$8=$B$2)/($I$3:$I$8<>""),COLUMNS($B5:B5))),"")
B6:D6	B6	=IFERROR(INDEX($H$3:$H$8,AGGREGATE(15,6,(ROW($H$3:$H$8)-ROW($H$3)+1)/($K$3:$K$8=$B$2)/($J$3:$J$8<>""),COLUMNS($B6:B6))),"")

 

[Browneh89]

Hey Fluff, Thank you for a response! unfortunately the formula did not work. I have a 3rd column in the table.. could that be what is throwing it out?

e.g.

 

[Fluff]

I have a 3rd column in the table.. could that be what is throwing it out?

Yes. Just change the range references in the formula to suit.

 

[Browneh89]

Yes. Just change the range references in the formula to suit.

Which range references? I had to change them all to suit the sheets I'm working on

 

[Fluff]

Which range references?

How would I know. I have absolutely no idea where your data is.

I had to change them all to suit the sheets I'm working on

Does it work?

 

[Browneh89]

How would I know. I have absolutely no idea where your data is.

Does it work?

Hahaha!

Nope unfortunately not, it does not pull any data at all.

 

[Fluff]

In that case where are all those ranges?

 

[Browneh89]

In that case where are all those ranges?

=IFERROR(INDEX('Restriction List'!$A$2:$A$100,AGGREGATE(15,6,(ROW('Restriction List'!$A$2:$A$100)-ROW('Restriction List'!$A$2)+1)/('Restriction List'!$E$2:$E$100='Use Me'!$C$8)/('Restriction List'!$C$2:$C$100<>""),COLUMNS('Use Me'!$G26:G26))),"")

So I'm attempting to pull the data from that sheet into the use me sheet.

 

[Fluff]

If the formula is in G26, C8 has the route & you want the store number for anything that needs Wrap, then that formula looks fine.
In what way is it not working?