×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Need help on Vlookup for 4 conditions

Need help on Vlookup for 4 conditions

Need help on Vlookup for 4 conditions

(OP)
Hi,

I am a new user of excel database functions and I need some help. For my application, I need to take 4 inputs from the front end, and get a value from the look up table. Can I use the vlookup formula for this?If yes, how can I use it for 4 conditions?I came across a lot of similar suggestions for using Nested IFs etc, but I dont know how exactly to apply it.

Can someone please help me?

RE: Need help on Vlookup for 4 conditions

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.

RE: Need help on Vlookup for 4 conditions

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!

RE: Need help on Vlookup for 4 conditions

(OP)
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?

RE: Need help on Vlookup for 4 conditions

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.

RE: Need help on Vlookup for 4 conditions

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.

RE: Need help on Vlookup for 4 conditions

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

RE: Need help on Vlookup for 4 conditions

(OP)
Thanks everyone!Concatenation and VLOOKUP worked like a charm!

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

RE: Need help on Vlookup for 4 conditions

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources