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?
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
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
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
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
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
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
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
Morten, I will look into DOE for sure. Thanks!
RE: Need help on Vlookup for 4 conditions
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