multiple lookups & ifs

S

se7098

i have posted this before and not rec'd a solution...posting again
desperately hoping for some help :)

I have one data sheet with information in columns a-j

i have a separate report that i need to populate from the data sheet

i need a formula that will go look for "charlotte" in column b of the data
sheet and if found then look for "customer assistant" in column d and if
found then find "job seeker" in the same data sheet and if found return the
value listed under "job seeker" to the report

so i think it should be a combo of vlookup and if statements but i am not
familiar with if statements and not very good with formulas.

Charlotte is in the data sheet along with customer assistant and job
seeker...customer assistant will be found in column D and job seeker may be
found potentially in columns E-I.

Example:

Charlotte
High Volume Title Job Seeker Testing Evaluation
Sales Associates 3 6 2
Cusomer Assistant 9 7 1

I need to look for charlotte and if found then look in the rows below for
customer assistant and then look for job seeker then if found look one row
down and return the value found directly below job seeker and return that
number to my report in a separate worksheet.

this is all very new to me so i will need it broken down in simple terms
please.

Thank for your help...this discussion board is the best excel resource i
have ever found.
 
S

smartin

se7098 said:
i have posted this before and not rec'd a solution...posting again
desperately hoping for some help :)

Patience, friend. Your previous post is answered.
 
M

Max

This assumes that your sample data as posted is representative of a repeating
data "packet" structure that you have on the sheet, where names (eg:
Charlotte) are unique in col B, with "Job Seeker" unique in the row just
below each name within cols B to I (8 cols, 8 = width param), and with
"Customer Assistant" unique within a 4 cell column range (in col D) next to
each name (4 = height param):

=OFFSET(INDIRECT("B"&MATCH("Charlotte",B:B,0)),MATCH("Customer
Assistant",OFFSET(INDIRECT("B"&MATCH("Charlotte",B:B,0)),,2,4),0)-1,MATCH("Job Seeker",OFFSET(INDIRECT("B"&MATCH("Charlotte",B:B,0)),1,,,8),0)-1)

In your sample data, the above will return: 9
ie the value at the intersection of "Job Seeker" & "Customer Assistant" for
"Charlotte"'s data range
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:370 Subscribers:66
xdemechanik
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top