Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup & multiple ifs
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 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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup & multiple ifs
Hi,
Your question isn't clear, after finding 'Charlotte' in Column A, where do we look for 'Job seeker'? Mike "se7098" wrote: 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 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup & multiple ifs
Sorry...Charlotte is in the data sheet along with job seeker...job seeker may
be found anywhere to the right of Charlotte (potentially in columns E-I). Example: Charlotte High Volume Title Job Seeker Testing Evaluation Hire I need to look for charlotte 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. "Mike H" wrote: Hi, Your question isn't clear, after finding 'Charlotte' in Column A, where do we look for 'Job seeker'? Mike "se7098" wrote: 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 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup & multiple ifs
Try this formula:
=HLOOKUP("Job Seeker",OFFSET(Sheet1!A1:I2,MATCH ("Charlotte",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-Åke On Wed, 17 Dec 2008 13:01:01 -0800, se7098 wrote: Sorry...Charlotte is in the data sheet along with job seeker...job seeker may be found anywhere to the right of Charlotte (potentially in columns E-I). Example: Charlotte High Volume Title Job Seeker Testing Evaluation Hire I need to look for charlotte 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. "Mike H" wrote: Hi, Your question isn't clear, after finding 'Charlotte' in Column A, where do we look for 'Job seeker'? Mike "se7098" wrote: 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 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup & multiple ifs
Hi Lars,
I'm trying it but need to know...what does sheet1a1-i2 represent so i can update it accordingly. thanks. "Lars-Ã…ke Aspelin" wrote: Try this formula: =HLOOKUP("Job Seeker",OFFSET(Sheet1!A1:I2,MATCH ("Charlotte",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-Ã…ke On Wed, 17 Dec 2008 13:01:01 -0800, se7098 wrote: Sorry...Charlotte is in the data sheet along with job seeker...job seeker may be found anywhere to the right of Charlotte (potentially in columns E-I). Example: Charlotte High Volume Title Job Seeker Testing Evaluation Hire I need to look for charlotte 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. "Mike H" wrote: Hi, Your question isn't clear, after finding 'Charlotte' in Column A, where do we look for 'Job seeker'? Mike "se7098" wrote: 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 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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup & multiple ifs
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 "Charlotte" 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-Åke On Wed, 17 Dec 2008 13:41:01 -0800, se7098 wrote: Hi Lars, I'm trying it but need to know...what does sheet1a1-i2 represent so i can update it accordingly. thanks. "Lars-Åke Aspelin" wrote: Try this formula: =HLOOKUP("Job Seeker",OFFSET(Sheet1!A1:I2,MATCH ("Charlotte",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-Åke On Wed, 17 Dec 2008 13:01:01 -0800, se7098 wrote: Sorry...Charlotte is in the data sheet along with job seeker...job seeker may be found anywhere to the right of Charlotte (potentially in columns E-I). Example: Charlotte High Volume Title Job Seeker Testing Evaluation Hire I need to look for charlotte 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. "Mike H" wrote: Hi, Your question isn't clear, after finding 'Charlotte' in Column A, where do we look for 'Job seeker'? Mike "se7098" wrote: 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 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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup & multiple ifs
Thanks 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: 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 "Charlotte" 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-Ã…ke On Wed, 17 Dec 2008 13:41:01 -0800, se7098 wrote: Hi Lars, I'm trying it but need to know...what does sheet1a1-i2 represent so i can update it accordingly. thanks. "Lars-Ã…ke Aspelin" wrote: Try this formula: =HLOOKUP("Job Seeker",OFFSET(Sheet1!A1:I2,MATCH ("Charlotte",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-Ã…ke On Wed, 17 Dec 2008 13:01:01 -0800, se7098 wrote: Sorry...Charlotte is in the data sheet along with job seeker...job seeker may be found anywhere to the right of Charlotte (potentially in columns E-I). Example: Charlotte High Volume Title Job Seeker Testing Evaluation Hire I need to look for charlotte 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. "Mike H" wrote: Hi, Your question isn't clear, after finding 'Charlotte' in Column A, where do we look for 'Job seeker'? Mike "se7098" wrote: 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 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple vlookup // | Excel Worksheet Functions | |||
Vlookup with Multiple criteria and multiple sheets | Excel Worksheet Functions | |||
Vlookup multiple value | Excel Worksheet Functions | |||
Vlookup for multiple criteria, multiple worksheets | Excel Worksheet Functions | |||
How do I use VLOOKUP to ref multiple workbooks with multiple tabs? | Excel Discussion (Misc queries) |