Need help on Vlookup for 4 conditions 1

Are you talking about a 4 dimensional lookup table? You'll struggle with vlookup, which is really designed for 2d.

Generally if you are trying something complex it is easier to use index and match

Can you post an example, your description is less than clear?



Cheers

Greg Locock

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

I'm not sure about using multiple VLOOKUP statements in this application, but you can certainly use nested IF statements. However, this is the way that I have accomplished a similar task:

Suppose Columns A to D contain your 4 search criteria; then you can create a single search criterion in Column E by concatenating Columns A to D.

E.g. suppose the four search criteria are single digit integers; then I can make a single 4 digit search criterion using:

=CONCATENATE(TEXT(A4,"0"),TEXT(B4,"0"),TEXT(C4,"0"),TEXT(D4,"0"))

(If your search criteria have different formats, you may need to experiment with formats to generate a suitable concatenated search criterion.)

Suppose Column F now contains the required results; sort the whole data block into ascending sequence by Column E (the combined search criteria), then you can find the required result by a single Vlookup.

For example, in the following expression, E21 contains the concatenated search criteria I am trying to match, E4 to E19 contains the set of all possible concatenated search criteria, and F4 to F19 contains the respective results.

=VLOOKUP(E21,E4:F19,2)

Hope this helps!

Thanks Julian!I will try that out.

Greg: you are right, my description is kinda vague! So here goes,

I have 4 inter-related variables: material, process, coating, style (the 4 columns in the look up table) which influence my cost (the value which I am trying to get from the table). The data comprises of every possible combination of the different materials, processes, coatings and style to give a different cost value for each combination. When user enters the particular material, process, coating and style, I want to look up the cost for that particular combination.

Any more thoughts on it?

Julian's method will work fine for that. That is, generate a key for each table entry, generate a key for the current enquiry, look the key up.

His method of generating keys will run into problems if there are more than 10 variables in any one field, there are obvious and less obvious solutions to that.




Cheers

Greg Locock

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

Greg Locock is right - the trick is just to come up with SOME way of generating a unique "key" for any possible combination of the 4 (or more) individual keys, and then sort the table by the single combined key so that Vlookup will find the match. (Vlookup expects the data table to be sorted.)

Some options for generating unique keys:

a) Concatenation as text strings, as described above - this will work for numeric or alpha data. If the range of any of your fields can go beyond the integer 9, you might want to consider concatenating two or more digits - e.g. 9, 17, 3, 21 could code to "09170321". For alpha fields, you might want to concatenate with spaces to generate a short "sentence"; e.g. Steel, Welded, Painted, Matte could code to "Steel Welded Painted Matte".

b) For numeric data, you can combine several integers into a single larger multi-digit integer. For multiple single-digit fields, you can use =A2*100+B2*10+C2 ; for two-digit fields you could try =A3*10000+B3*100+C3 ; and so on.

To make sure that the user enters ONLY valid data in all of the fields, you might want to consider using "Data Validation" to restrict the input choices to a limited List, rather than allow free-field input. This can be very important when using Vlookup, because it can return an approximate match, unless you are careful to use the "range_lookup" switch option. Depending upon your application, approximate matches may be acceptable, or they could be catastrophic.

I am sure you can come up with some sort of coding scheme which will work for your application.

jinx2311

I assume that you have your four variables (material, process, coating, style) in discrete values and then for combinations of say material A B, Proscess a b coating 1 2 and styling % & then you wil have a table looking somewhat like this:

Mat Pro coat style cost
A a 1 % 1
A a 1 & 2
A a 2 % 1.5
.
.
.

Wouldnt it be easier to use the statistical principles from "Factorial Design" to produce a formula where you can calculate the cost for any combination instead of looking it up? An excellent text book on this subject is "Design and analysis of experiments" By Douglas C. Montgomery. It covers a lot of other items but has a lot dealing with your subject. BTW such an analysis will also confirm if you have a significant effect from your variables and if cross effects exists. (in fact your problem is very text book like...)

Best regards

Morten

Thanks everyone!Concatenation and VLOOKUP worked like a charm!

Morten, I will look into DOE for sure. Thanks!

There is a non-elegant way to use vlookup in 3D.

Use adjacent tables of similar data, for example

Load case 1 Load case 2
Node num. stress Node num. stress
1 300 1 270
2 220 2 200
3 124 3 116
4 340 4 320

Then for a cell that returns the stress at node 3 for load case 2, use vlookup like this:

Vlookup(cell1, whole range (all data tables), vlookup(&&&), false)

Where the vlookup command inside the bracket looks up a range of column offsets, based on load case numbers.

It works like a charm.

tg