Lookup data by matching 4 columns

Hey,

I am trying to get excel to look up a value in a sheet based on a combination of 4 drop-down lists. What I have is a series of different formulations which consist of different particles, different particle addition, different fluids combinations and different levels of fluid combinations.

eg: A B C D E
formulation 1: 0.02 % Gravel in 0.1% Petrol W
formulation 2: 0.01 % Gravel in 0.1% Petrol X
formulation 3: 0.1 % Gravel in 0.1% Petrol Y
formulation 3: 1 % Gravel in 1% Petrol Z


What I want is, when I select a particular combination of columns A, B, C, D, for excel to then return the values in Column E

I hope this explanation makes sense

I have tried the VLOOKUP function, but I can't see how to make it lookup more than one column

If anyone has an idea how to do this it would be great

Cheers

hi,

it can be done by sumproduct, can you please upload the sample sheet to work out.

I hope I have understood you correctly

You could try =a1&" "&b1&" "&c1&" "&d1 this will combine all four columns into one cell

change the 1's to the relevant row number

if you want combin all the value in a row...

please use this...

=CONCATENATE(A1,B1,C1,D1)

Hey,

Just to clarify, here is a brief example of what I am looking for

Example.xlsx

hi,

=DGET($O$1:$S$16,S1,A7:D8)

see the attachment

Thanks for this.
It works well for the first array of data (Gravel), but when you select a different particle (Sand) type it fails. Can it be combined with an IF function?

=IF(B8="Gravel", DGET($O$1:$S$16,S1,A7:D8), IF(B8="Sand", DGET($U$1:$Y$16,Y1,A7:D8), IF(D8="Cement", DGET($AA$1:$AE$16,AE1,A7:D8)))))


* It appears there is a problem with this syntax, I am not sure why though

Excellent!! Thanks for this.
Should I take it that there isn't a method to have the data tables formatted in the way I had previously? I have a whole heap of data and I'd prefer to have them across the screen rather than down

Cheers