Lookup data based on 3 criteria

[haas786]

Hi all!

I need help with a problem I'm having with a lookup. I was wondering
if I can lookup a text entry from a list based on 4 criteria. Cells
E3, E4, E5, and E6 will contain user selected values which would in
turn return a corresponding value from a list based on what's in the
aforementioned cells. This value will be stored in cell A10. The
lookup list is on another sheet in the Excel workbook. I've been
ableto do it with 2 criteria, but can't make it work with 4. Please
help!

 

[Pete_UK]

Presumably you have columns in your table on the other sheet which
correspond to the user's choices in cells E3 to E6?

One way would be to insert a new column in your data table, say column
E, and join the other 4 columns together with a formula like:

=A1&B1&C1&D1

and copy this down. Now you can use VLOOKUP on this column, along the
lines of:

=VLOOKUP(E3&E4&E5&E6,Sheet2!E$1:F$200,2,0)

Hope this helps.

Pete

 

[Dave Peterson]

I think...

Saved from a previous post:

If you want exact matches for just two columns (and return a value from a
third), you could use:

=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100),0))

(all in one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.

This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.

And you can add more conditions by just adding more stuff to that product
portion of the formula:

=index(othersheet!$d$1:$d$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100)
*(c2=othersheet!$c$1:$c$100),0))

 

[haas786]

Presumably you have columns in your table on the other sheet which
correspond to the user's choices in cells E3 to E6?

One way would be to insert a new column in your data table, say column
E, and join the other 4 columns together with a formula like:

=A1&B1&C1&D1

and copy this down. Now you can use VLOOKUP on this column, along the
lines of:

=VLOOKUP(E3&E4&E5&E6,Sheet2!E$1:F$200,2,0)

Hope this helps.

Pete




- Show quoted text -

Thanks...this is a great idea, but i can't add any more columns to the
list. There's gotta be a simpler way to lookup data based on 4
criteria's. Thanks again for your help.

 

[haas786]

I think...

Saved from a previous post:

If you want exact matches for just two columns (and return a value from a
third), you could use:

=index(othersheet!$c$1:$c$100,
   match(1,(a2=othersheet!$a$1:$a$100)
          *(b2=othersheet!$b$1:$b$100),0))

(all in one cell)

This is an array formula.  Hit ctrl-shift-enter instead of enter.  If you do it
correctly, excel will wrap curly brackets {} around your formula.  (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007..

This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.

And you can add more conditions by just adding more stuff to that product
portion of the formula:

=index(othersheet!$d$1:$d$100,
   match(1,(a2=othersheet!$a$1:$a$100)
          *(b2=othersheet!$b$1:$b$100)
          *(c2=othersheet!$c$1:$c$100),0))

Dave,

Thanks for your reply...i need to have the value returned in a certain
cell; I don't understand the context of the formula above for using a
whole column.

 

[Dave Peterson]

You can't use a whole column unless you're using xl2007.

But I don't think I understand your question.