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
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

BSWJON

Board Regular
Joined
Mar 24, 2014
Messages
86
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?
 

photologue

New Member
Joined
Jun 12, 2014
Messages
13
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
 

vds1

Well-known Member
Joined
Oct 5, 2011
Messages
1,194
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
 

mfexcel

Well-known Member
Joined
Jan 8, 2009
Messages
791
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,567
Messages
5,625,570
Members
416,119
Latest member
JCLLE

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
Top