Looking Up Text entries

[haas786]

Hi all,

No one could help me with this before so I will repost hoping it'll be
different this time. I need to lookup a value in a worksheet of data
based on 4 different criteria. I can use Vlookup for looking up data
based on 1 criteria, and I can use SUMPRODUCT to look up values based
on multiple criteria, but I need to lookup a text entry based on 4
criteria. For example, in a worksheet in Excel I have 10 columns of
data. I would like to know the name of the person who lives in the
state of New York, was born in 1980, is a male, and drives an
Oldmobile. This is really done for illustration purposes and is not
exactly what I'm looking for, but just an example of it. Can someone
please help?

Thanks!

 

[Matt Richardson]

Hi all,

No one could help me with this before so I will repost hoping it'll be
different this time. I need to lookup a value in a worksheet of data
based on 4 different criteria. I can use Vlookup for looking up data
based on 1 criteria, and I can use SUMPRODUCT to look up values based
on multiple criteria, but I need to lookup a text entry based on 4
criteria. For example, in a worksheet in Excel I have 10 columns of
data. I would like to know the name of the person who lives in the
state of New York, was born in 1980, is a male, and drives an
Oldmobile. This is really done for illustration purposes and is not
exactly what I'm looking for, but just an example of it. Can someone
please help?

Thanks!

You could use the database function DGET, which would enable you to
use various criteria. Its a little hard to explain how the database
functions operate, but here are a few good links on how to use them:-

http://office.microsoft.com/en-us/excel/HP052090591033.aspx
http://www.meadinkent.co.uk/xldsumdavg.htm

http://www.cpearson.com/Newsletter/Content/2007_10_08.htm

The last one is personally my favourite and has an example you can
download to try yourself. It's well worth getting your head around
these.

Hope this helps - let us know how you get on.

Regards,
Matt Richardson
http://teachr.blogspot.com

 

[Rick Rothstein \(MVP - VB\)]

Assuming you don't decide to use the suggestion that Matt has given you, you
can do something like the following.

You didn't give us your layout, so I am going to assume one. Let's say your
names are in Column A, the state is in Column B, the year is in Column C,
the gender is in Column D and the make of car is in Column E. Also assume
your data starts in Row 2 (where I'm assuming Row 1 is a header row. Further
assume the lookup criteria are placed in cells (so they can be changed in
order to perform other look ups) as follows... G1 contains the state to
search for, G2 contains the year to search for, G3 contains the gender to
search for and G4 contains the make of car to search for; then this formula
will return the name located in Column A where all criteria match those
specified in the G1:G4...

=INDEX(A2:A10000,10000-SUMPRODUCT(MIN(10000-ROW(A2:A10000)*(B2:B10000=G1)*(C2:C10000=G2)*(D210000=G3)*(E2:E10000=G4)))-1)

where I am assuming you will have no more than 10000 records maximum. If
none of your data matches the criteria specified in G1:G4, then an #VALUE!
error is returned.

Rick