Assuming your table cannot have duplicates, (for example I changed row 3 so
that it duplicates row 1),

1 1 1 0 1
1 1 1 1 2
1 1 1 0 3

and this table is in A1:E100 and the user input is in G1:J1, you could use

=Sumproduct(--($A$1:$A$100=G1), --($B$1:$B$100=H1), --($C$1:$C$100=I1),
--($D$1:$D$100=J1), $E$1:$E$100)

The 1's could be replaced w/ a cell reference for the user input.

Or, if the table can have duplicate keys you could use the following to
return the first instance (assuming G1:J1 is the user input)

=Index($E$1:$E$100, MATCH(G1&" "&H1&" "&I1&" "&J1, $A$1:$A$100&"
"&$B$1:$B$100&" "&$C$1:$C$100&" "&$D$1:$D$100,0))

entered using Cntrl+Shift+Enter (instead of just the enter key).

Or you could also insert a column to the left of your table (so now your
table will occupy A1:F100). In A1 enter

=B1&" "&C1&" "&D1&" "&E1
and copy down

Assuming the user input is still in G1:J1
=VLookup(G1&" "&H1&" "&I1&" "&J1, $A$1:$F$100, 6, 0)



Sumproduct won't work with duplicate keys (for what you are doing). The
Index/Match formula can adversely affect performance if you have a lot of
these formulae. And the Vlookup will require a helper column.



" wrote:

Hello,

I have never used the more advanced features of excel, usually
just the common math operations. However I need to do a complex lookup
and I need all the help I can get.

I have one worksheet populated with a database. The database looks like
the following.

bk ch ver nt id
1 1 1 0 1
1 1 1 1 2
1 1 1 2 3
1 1 1 3 4
1 1 1 4 5
1 1 1 5 6
1 1 2 0 7
1 1 2 1 8
1 1 2 2 9


I want to do a lookup that returns the value in the ID cell when the
user types in the previous 4 values in 4 seperate cells.

What I mean is, the user will be asked to input data into 4 cells. If
the user types:

1 1 2 1 --- 8 will be returned in a fifth cell.

This kind of input should work for every row in the worksheet. This way
a user can type:

1 1 2 1 get 8 (all on the same row) then go to the
next row and type
1 1 1 4 get 5 then next row (all on the same row)
etc...

I want it so that the data is in one worksheet and the lookup is in
another blank worksheet. blank so that the user can populate it.

Any help would be greatly appreciated!

Joe