Lookup Based on 4 values

[toomanyjoes]

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

 

[Guest]

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.

 

[toomanyjoes]

Worked like a charm. Because all the rows are unique I used Sumproduct:

=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)

Also I just swapped the array ranges out for predefined names like
this:

=Sumproduct(--(book=G1), --(chapter=H1), --(verse=I1), --(ft=J1), id)

The names were all defined in a different worksheet so the table is
hidden from the user while they are inputting data.

I'm not exactly sure why it works, but it works! Thank you.

May I ask why there is a need for the spaces with the index function?
I'll change your code to reflect my naming convention.

=Index(id, MATCH(G1&" "&H1&" "&I1&" "&J1, book&" "&chapter&" "&verse&"
"&ft,0))

Why is there a need for the space inbetween the cells and arrays &" "&?

Thanks again,

Joe

 

[Guest]

More on sumproduct here
http://xldynamic.com/source/xld.SUMPRODUCT.html

Regarding the spaces in the index/match, if you had

1 13 4
11 3 4

these two series would be identical if concatenated w/o some sort of separator

1134
1134

so I often include a separator. Of course, it could probably be redone so
that a separator is not needed

=INDEX(id, MATCH(1, (book=G1)*(chapter=H1)*(verse=I1)*(ft=J1),0))

Note that excel stores TRUE as 1 and FALSE as 0. When TRUE/FALSE values
have some arithmetic operation performed on them, excel converts them to 1/0.
So the above would create an array of 1's and 0's with the 1's being the
items where all 4 conditions are TRUE.