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!

 

[Browneh89]

That's asking me to sign in, you need to mark the file for sharing & then post the link that you get.

TEST.xlsm

drive.google.com

Yup I think I did it the wrong way around

 

[Fluff]

Thanks for that.
6466 does not exist in the table, try changing it to 6446

Also the values in E49 & E50 are text not numbers.
Also because of the merged cells when your drag the formula in G26 to the right it becomes COLUMNS('Use Me'!$G26:J26) which won't work.
Use

=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<>""),1)),"")

and then change the red 1 to a 2 for J26, and a 3 for M26

 

[Browneh89]

Thanks for that.
6466 does not exist in the table, try changing it to 6446

Also the values in E49 & E50 are text not numbers.
Also because of the merged cells when your drag the formula in G26 to the right it becomes COLUMNS('Use Me'!$G26:J26) which won't work.
Use

=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<>""),1)),"")

and then change the red 1 to a 2 for J26, and a 3 for M26

Darn can't believe it was so obvious! thank you for all of your help!

 

[Fluff]

You're welcome & thanks for the feedback.