vlookup & multiple ifs

vlookup & multiple ifs

Post by c2U3MDk » Fri, 19 Dec 2008 05:39:01


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 "c *** te" in column a of the data
sheet 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.

Thanks.
 
 
 

vlookup & multiple ifs

Post by TWlrZSB » Fri, 19 Dec 2008 05:57:00

Hi,

Your question isn't clear, after finding 'C *** te' in Column A, where do
we look for 'Job seeker'?

Mike

 
 
 

vlookup & multiple ifs

Post by c2U3MDk » Fri, 19 Dec 2008 06:01:01

Sorry...C *** te is in the data sheet along with job seeker...job seeker may
be found anywhere to the right of C *** te (potentially in columns E-I).

Example:

C *** te
High Volume Title Job Seeker Testing Evaluation
Hire

I need to look for c *** te and if found then look one row down and 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.

Thanks.
 
 
 

vlookup & multiple ifs

Post by Lars-e Asp » Fri, 19 Dec 2008 06:20:40

Try this formula:

=HLOOKUP("Job Seeker",OFFSET(Sheet1!A1:I2,MATCH
("C *** te",Sheet1!A1:A100,0),0),2,FALSE)

all in one line

Sheet1 is the name of the sheet with the data table.
Change the 100 to match then number of data rows.

Hope this helps / Lars-舓e

On Wed, 17 Dec 2008 13:01:01 -0800, se7098
 
 
 

vlookup & multiple ifs

Post by c2U3MDk » Fri, 19 Dec 2008 06:41:01

Hi Lars,

I'm trying it but need to know...what does sheet1a1-i2 represent so i can
update it accordingly.

thanks.
 
 
 

vlookup & multiple ifs

Post by Lars-e Asp » Fri, 19 Dec 2008 06:49:58

It represents the two first rows of your data table. Change it to
A1:J2 if you want. It doesn't matter.
You can also change it to E1:I2 as you said that "Job seeker" will
always be found in columns E to I.

The OFFSET function "moves" this range to the two rows below the row
where "C *** te" is found.

Finally the HLOOKUP function searches for "Job seeker" and returns the
value on the second row, i.e. just below "Job seeker".

/ Lars-舓e


On Wed, 17 Dec 2008 13:41:01 -0800, se7098
 
 
 

vlookup & multiple ifs

Post by c2U3MDk » Fri, 19 Dec 2008 07:05:00

hanks for your help Lars, however, I'm trying it but receiving a #n/a
error...below is my formula:

=HLOOKUP("Job Seeker",OFFSET('[Open Reqs High Volume.xls]Summary by City and
Step'!$A$1:$J$2,MATCH("Charlotte",'[Open Reqs High Volume.xls]Summary by City
and Step'!$A$1:$J$506,0),0),2,FALSE)

What does the 2 before false represent? Thanks again for your help.

"Lars-脜ke Aspelin" wrote: