Lookup, Match &Index - Help with tables containing Duplicates and Complex Lookups

[briggss1]

The problem is not that complicated, but my abilities run a bit short without burning days. I always have to refresh my brain on these lookup formulas.

In sheet 1 this table needs to match the data in all 3 columns (A-C) to properly aurtopopulate the resulting value in Column D, "Weight." Column A-C are selected off of a picklist. So the lookup is searching a table in Sheet 2 that contains reference weights (Limits).

Sheet1

This is the data in Sheet 2 that holds the reference data I seek (Weight) corresponding to what I selected in Sheet1 Columns A-C). In real life, this is actually pulling this data from 5000-10000 rows of information and all kinds of iterations based on what I select in Columns A-C.

Sheet 2

Any help will be greatly appreciated.

 

[AhoyNC]

What version of Excel are you using?

 

[briggss1]

Microsoft® Excel® for Microsoft 365 MSO (Version 2208 Build 16.0.15601.20204) 64-bit

 

[AhoyNC]

Try:
Sheet 1

Book2
	A	B	C	D
1
2	Mat	Jur	Cat	Weight
3	Leather	USA	rawhide	1.8
4	Leather	Mexico	rawhide	1.7
5	Leather	USA	Suede	1.4
6	Leather	China	rawhide	1.68
Sheet1

Cell Formulas
Range	Formula
D3:D6	D3	=FILTER(Sheet2!$D$2:$D$5,(Sheet2!$A$2:$A$5=A3)*(Sheet2!$B$2:$B$5=B3)*(Sheet2!$C$2:$C$5=C3),"No Match")

Sheet 2

Book2
	A	B	C	D
1	Mat	Jur	Cat	Weight
2	Leather	USA	rawhide	1.8
3	Leather	USA	Suede	1.4
4	Leather	Mexico	rawhide	1.7
5	Leather	China	rawhide	1.68
Sheet2

 

[Fluff]

Microsoft® Excel® for Microsoft 365 MSO (Version 2208 Build 16.0.15601.20204) 64-bit

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[briggss1]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I would, but I use 2-3 different versions. Depends on where I am working or at home.

 

[Fluff]

Ok. But you would be advised to say which version you need it to work on in your op. That way it saves members having to ask.

 

[briggss1]

Try:
Sheet 1

Book2
	A	B	C	D
1
2	Mat	Jur	Cat	Weight
3	Leather	USA	rawhide	1.8
4	Leather	Mexico	rawhide	1.7
5	Leather	USA	Suede	1.4
6	Leather	China	rawhide	1.68
Sheet1

Cell Formulas
Range	Formula
D3:D6	D3	=FILTER(Sheet2!$D$2:$D$5,(Sheet2!$A$2:$A$5=A3)*(Sheet2!$B$2:$B$5=B3)*(Sheet2!$C$2:$C$5=C3),"No Match")

Sheet 2

Book2
	A	B	C	D
1	Mat	Jur	Cat	Weight
2	Leather	USA	rawhide	1.8
3	Leather	USA	Suede	1.4
4	Leather	Mexico	rawhide	1.7
5	Leather	China	rawhide	1.68
Sheet2

worked like a boss. I am forever thankful to this board and its members.

 

[AhoyNC]

You're welcome. Thanks for the feedback.