Two criteria for a lookup table

photologue

New Member
Joined
Jun 12, 2014
Messages
13
Good morning,

I have the following lookup table ...
ProductPrintsMaterialPrinitedTinPackaging
LuggageTags0.260.951.210.000.36
FridgeMagnets0.220.690.910.000.39
Keyring0.220.901.120.000.21
Coaster0.110.480.590.000.08
ToteBag0.893.684.570.000.76
MouseMat0.210.791.000.000.26
Jigsaw0.451.201.650.830.25
WhiteMug11oz0.250.951.200.000.30
BlackMug11oz0.311.271.580.000.30
WhiteMug15oz0.311.151.460.000.40
Photopanel7x50.753.504.250.000.24
Photopanel10x81.064.956.010.000.36

<colgroup><col><col><col span="3"><col></colgroup><tbody>
</tbody>

I then have two drop down lists for selecting the product (column G) and the build stage (column F) (prints, material .... packaging).

I want to return the relevant cost based on BOTH product and build stage criteria and know that my current method is not best practice.

Can anyone please point me in the right direction ?

Many thanks in advance
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
are columns G and F immediately adjacent to your packaging column, and do the values in the tables represent the costs associated to the criteria in the column heading?

also what is your current method?
 
Upvote 0
No, I have placed the lookup table out of harms way !
It is currently AT2 to AY14.
Yes, the values in the table represent the costs associated to the criteria in the column heading.
My workaround at the moment is :-
=IF(F3="Prints", VLOOKUP(G3,AT2:AY14,2,FALSE),"")&IF(F3="Material", VLOOKUP(G3,AT2:AY14,3,FALSE),"")&IF(F3="Printed", VLOOKUP(G3,AT2:AY14,4,FALSE),"")&IF(F3="Tin", VLOOKUP(G3,AT2:AY14,5,FALSE),"")&IF(F3="Packaging", VLOOKUP(G3,AT2:AY14,6,FALSE),"")

Thanks
 
Upvote 0
Try,

=INDEX(A1:F13,MATCH(G1,A1:A13,0),MATCH(H1,A1:F1,0))

WHERE

A1:F13 > Lookup table
A1:A13 > Product column in Lookup Table
A1:F1 > Field name in Lookup Table

G1 > Product like ToteBag,MouseMat,Jigsaw.....
H1 > Field name like Prints,Material,Prinited..... etc

HTH
 
Upvote 0
For your reference:
Perform VLOOKUP with 2 lookup values | wmfexcel


Good morning,

I have the following lookup table ...
ProductPrintsMaterialPrinitedTinPackaging
LuggageTags0.260.951.210.000.36
FridgeMagnets0.220.690.910.000.39
Keyring0.220.901.120.000.21
Coaster0.110.480.590.000.08
ToteBag0.893.684.570.000.76
MouseMat0.210.791.000.000.26
Jigsaw0.451.201.650.830.25
WhiteMug11oz0.250.951.200.000.30
BlackMug11oz0.311.271.580.000.30
WhiteMug15oz0.311.151.460.000.40
Photopanel7x50.753.504.250.000.24
Photopanel10x81.064.956.010.000.36

<tbody>
</tbody>

I then have two drop down lists for selecting the product (column G) and the build stage (column F) (prints, material .... packaging).

I want to return the relevant cost based on BOTH product and build stage criteria and know that my current method is not best practice.

Can anyone please point me in the right direction ?

Many thanks in advance
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,733
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top