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
 

BSWJON

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

Forum statistics

Threads
1,085,460
Messages
5,383,811
Members
401,853
Latest member
Thoro

Some videos you may like

This Week's Hot Topics

Top