Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup and 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 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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup and multiple ifs
se7098 wrote:
[snipped] 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. You were not quite consistent in describing what you want, but I think what you are saying is you want the value under "Job Seeker", in the row for "Cusomer Assistant" [sic], under the heading "Charlotte". Here is one way that uses a few "helper columns". Since you say you are new to some of this I will describe each formula. I'm guessing you might want to extend this to look for other cities, other titles, so I worked that in too. Put the following in K1:N1 Charlotte Cusomer Assistant Job Seeker Value In K2 =MATCH(K$1,$B:$B,0) This looks for the city name in column B and tells which row has it. In L2 =MATCH(L$1,OFFSET(INDIRECT("B"&K2),0,2,999),0)-1 This looks for the title (Cusomer Assistant) in column D, and tells how many rows below the city name it appears. In M2 =MATCH(M$1,OFFSET(INDIRECT("B"&K2),1,0,1,8),0)-1 This scans the row below the city name for the opportunity (Job Seeker) and tells how many columns to the right of B is appears. In N2 =OFFSET(INDIRECT("B"&K2),L2,M2) Finally, the value you are looking for! It uses the coordinates we determined in L2 and M2 to locate the correct value. With that done, should you want to find a value for another city, title, etc., just change the cells K1:M1. Hope this helps! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup and multiple ifs
Thanks for the response...i have a few questions.
Do i enter these formulas in the data sheet or the report i am trying to populate? Also, how do i get it to return the value into the correct cell on my report? Thanks again for your help! "smartin" wrote: se7098 wrote: [snipped] 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. You were not quite consistent in describing what you want, but I think what you are saying is you want the value under "Job Seeker", in the row for "Cusomer Assistant" [sic], under the heading "Charlotte". Here is one way that uses a few "helper columns". Since you say you are new to some of this I will describe each formula. I'm guessing you might want to extend this to look for other cities, other titles, so I worked that in too. Put the following in K1:N1 Charlotte Cusomer Assistant Job Seeker Value In K2 =MATCH(K$1,$B:$B,0) This looks for the city name in column B and tells which row has it. In L2 =MATCH(L$1,OFFSET(INDIRECT("B"&K2),0,2,999),0)-1 This looks for the title (Cusomer Assistant) in column D, and tells how many rows below the city name it appears. In M2 =MATCH(M$1,OFFSET(INDIRECT("B"&K2),1,0,1,8),0)-1 This scans the row below the city name for the opportunity (Job Seeker) and tells how many columns to the right of B is appears. In N2 =OFFSET(INDIRECT("B"&K2),L2,M2) Finally, the value you are looking for! It uses the coordinates we determined in L2 and M2 to locate the correct value. With that done, should you want to find a value for another city, title, etc., just change the cells K1:M1. Hope this helps! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup and multiple ifs
se7098 wrote:
Thanks for the response...i have a few questions. Do i enter these formulas in the data sheet or the report i am trying to populate? Also, how do i get it to return the value into the correct cell on my report? Thanks again for your help! Theoretically you can put the formulas anywhere you prefer, but the references will need to be changed manually. The final value cell can go anywhere, just make sure it points to the other three supporting formulas. And kudos to you by the way for trying to plow through this--these are not the easiest formulas to master (I'm still trying myself!) Formula #1 =MATCH(K$1,$B:$B,0) ^^^ ^^^^^ K$1 points to the "selector cell" where you enter a city name. $B:$B points to the data sheet. If the formulas are in a different worksheet this will look something like 'Data Sheet'!$B:$B. If you use the formula wizard the correct sheet name will be filled in for you. Formula #2 =MATCH(L$1,OFFSET(INDIRECT("B"&K2),0,2,999),0)-1 ^^^ ^ ^^ L$1 points to the title selector, similar to above. INDIRECT("B"&K2) -- Things get trickier with the INDIRECT formulas. "B" refers to the left-most column in the data sheet, the "anchor column" if you will. Again, if the data and formulas are in different worksheets you again need to ensure the sheet reference is correct, unfortunately the wizard will not help you with this one, so you need to insert the correct reference yourself e.g., INDIRECT("'Data Sheet'!B"&K2) K2 points to formula #1, easily enough. The remaining formulas are similar to above. These are the spots you will need to watch: Formula #3 =MATCH(M$1,OFFSET(INDIRECT("B"&K2),1,0,1,8),0)-1 ^^^ ^ ^^ Formula #4 =OFFSET(INDIRECT("B"&K2),L2,M2) ^ ^^ ^^ ^^ |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup and multiple ifs
Thanks again for your help...you are correct in that these formulas are WAY
over my head...but i love this stuff and am determined to learn it. :) so i REALLY appreciate your help and patience. the 1st formula is working. however the remainder are not. Below is my version of formula 2: =MATCH(B380,OFFSET(INDIRECT("[In Process Reqs.xls]Summary by City and Step'!$B:$B"&BV380),0,2,999),0)-1 b380=title of position in process reqs=my data sheet bv380=value of my first formula Any ideas? Thanks again for your help! "smartin" wrote: se7098 wrote: Thanks for the response...i have a few questions. Do i enter these formulas in the data sheet or the report i am trying to populate? Also, how do i get it to return the value into the correct cell on my report? Thanks again for your help! Theoretically you can put the formulas anywhere you prefer, but the references will need to be changed manually. The final value cell can go anywhere, just make sure it points to the other three supporting formulas. And kudos to you by the way for trying to plow through this--these are not the easiest formulas to master (I'm still trying myself!) Formula #1 =MATCH(K$1,$B:$B,0) ^^^ ^^^^^ K$1 points to the "selector cell" where you enter a city name. $B:$B points to the data sheet. If the formulas are in a different worksheet this will look something like 'Data Sheet'!$B:$B. If you use the formula wizard the correct sheet name will be filled in for you. Formula #2 =MATCH(L$1,OFFSET(INDIRECT("B"&K2),0,2,999),0)-1 ^^^ ^ ^^ L$1 points to the title selector, similar to above. INDIRECT("B"&K2) -- Things get trickier with the INDIRECT formulas. "B" refers to the left-most column in the data sheet, the "anchor column" if you will. Again, if the data and formulas are in different worksheets you again need to ensure the sheet reference is correct, unfortunately the wizard will not help you with this one, so you need to insert the correct reference yourself e.g., INDIRECT("'Data Sheet'!B"&K2) K2 points to formula #1, easily enough. The remaining formulas are similar to above. These are the spots you will need to watch: Formula #3 =MATCH(M$1,OFFSET(INDIRECT("B"&K2),1,0,1,8),0)-1 ^^^ ^ ^^ Formula #4 =OFFSET(INDIRECT("B"&K2),L2,M2) ^ ^^ ^^ ^^ |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup and multiple ifs
se7098 wrote:
Thanks again for your help...you are correct in that these formulas are WAY over my head...but i love this stuff and am determined to learn it. :) so i REALLY appreciate your help and patience. the 1st formula is working. however the remainder are not. Below is my version of formula 2: =MATCH(B380,OFFSET(INDIRECT("[In Process Reqs.xls]Summary by City and Step'!$B:$B"&BV380),0,2,999),0)-1 b380=title of position in process reqs=my data sheet bv380=value of my first formula Any ideas? Thanks again for your help! LOL I knew I should not have deleted the test worksheet I built for this project! So, on the fly, I think you could try this: =MATCH(B380,OFFSET(INDIRECT("'[In Process Reqs.xls]Summary by City and Step'!B"&BV380),0,2,999),0)-1 Two issues to note: Missing tick mark ' in front of external workbook reference Do not reference the whole column $B:$B in the INDIRECT function. If you look at how we are using INDIRECT, we are building up a string that will serve as a cell reference. E.g., if BV380 has the value 10, then INDIRECT("'[In Process Reqs.xls]Summary by City and Step'!B"&BV380) becomes INDIRECT("'[In Process Reqs.xls]Summary by City and Step'!B10) which boils down to ='[In Process Reqs.xls]Summary by City and Step'!B10 .... just a simple cell reference! The beauty of it is we can change the row (or column) by inserting other formulas and wrapping it with INDIRECT. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup and multiple ifs
smartin wrote:
se7098 wrote: Thanks again for your help...you are correct in that these formulas are WAY over my head...but i love this stuff and am determined to learn it. :) so i REALLY appreciate your help and patience. the 1st formula is working. however the remainder are not. Below is my version of formula 2: =MATCH(B380,OFFSET(INDIRECT("[In Process Reqs.xls]Summary by City and Step'!$B:$B"&BV380),0,2,999),0)-1 b380=title of position in process reqs=my data sheet bv380=value of my first formula Any ideas? Thanks again for your help! LOL I knew I should not have deleted the test worksheet I built for this project! So, on the fly, I think you could try this: =MATCH(B380,OFFSET(INDIRECT("'[In Process Reqs.xls]Summary by City and Step'!B"&BV380),0,2,999),0)-1 Two issues to note: Missing tick mark ' in front of external workbook reference Do not reference the whole column $B:$B in the INDIRECT function. If you look at how we are using INDIRECT, we are building up a string that will serve as a cell reference. E.g., if BV380 has the value 10, then INDIRECT("'[In Process Reqs.xls]Summary by City and Step'!B"&BV380) becomes Whoops! I got off track here. INDIRECT('[In Process Reqs.xls]Summary by City and Step'!B10) which boils down to the range '[In Process Reqs.xls]Summary by City and Step'!B10 .... just a simple reference! The beauty of it is we can change the row (or column) by inserting other formulas and wrapping it with INDIRECT. Sorry for the confusion. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup and multiple ifs
sorry...i'm sure you are sick of me, but i am still getting a #ref! error
so if the result of bv380 is 33 does that mean the formula in bw380 is looking in line 33 of my data sheet? would it help if i sent you my actual spreadsheet or would that be asking too much or too cumbersome? thanks again... "smartin" wrote: se7098 wrote: Thanks again for your help...you are correct in that these formulas are WAY over my head...but i love this stuff and am determined to learn it. :) so i REALLY appreciate your help and patience. the 1st formula is working. however the remainder are not. Below is my version of formula 2: =MATCH(B380,OFFSET(INDIRECT("[In Process Reqs.xls]Summary by City and Step'!$B:$B"&BV380),0,2,999),0)-1 b380=title of position in process reqs=my data sheet bv380=value of my first formula Any ideas? Thanks again for your help! LOL I knew I should not have deleted the test worksheet I built for this project! So, on the fly, I think you could try this: =MATCH(B380,OFFSET(INDIRECT("'[In Process Reqs.xls]Summary by City and Step'!B"&BV380),0,2,999),0)-1 Two issues to note: Missing tick mark ' in front of external workbook reference Do not reference the whole column $B:$B in the INDIRECT function. If you look at how we are using INDIRECT, we are building up a string that will serve as a cell reference. E.g., if BV380 has the value 10, then INDIRECT("'[In Process Reqs.xls]Summary by City and Step'!B"&BV380) becomes INDIRECT("'[In Process Reqs.xls]Summary by City and Step'!B10) which boils down to ='[In Process Reqs.xls]Summary by City and Step'!B10 .... just a simple cell reference! The beauty of it is we can change the row (or column) by inserting other formulas and wrapping it with INDIRECT. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup and multiple ifs
OMG!OMG!OMG!OMG!OMG!OMG!OMG!OMG!OMG!OMG!OMG!OMG!OM G!OMG!
I DID IT!!! Well with signficiant eversomuchappreciated help from you! this is the most awesome thing ever! I still can't believe it actually worked and that i was able to actually troubleshoot a few things on my own...i present this to my boss tomorrow. i cannot possibly tell you how much i appreciate your help with this...honestly, i never thought any of it would ever work but it does and it is beautiful! thanks SO VERY MUCH again for your help! you are THE BEST!!! "smartin" wrote: smartin wrote: se7098 wrote: Thanks again for your help...you are correct in that these formulas are WAY over my head...but i love this stuff and am determined to learn it. :) so i REALLY appreciate your help and patience. the 1st formula is working. however the remainder are not. Below is my version of formula 2: =MATCH(B380,OFFSET(INDIRECT("[In Process Reqs.xls]Summary by City and Step'!$B:$B"&BV380),0,2,999),0)-1 b380=title of position in process reqs=my data sheet bv380=value of my first formula Any ideas? Thanks again for your help! LOL I knew I should not have deleted the test worksheet I built for this project! So, on the fly, I think you could try this: =MATCH(B380,OFFSET(INDIRECT("'[In Process Reqs.xls]Summary by City and Step'!B"&BV380),0,2,999),0)-1 Two issues to note: Missing tick mark ' in front of external workbook reference Do not reference the whole column $B:$B in the INDIRECT function. If you look at how we are using INDIRECT, we are building up a string that will serve as a cell reference. E.g., if BV380 has the value 10, then INDIRECT("'[In Process Reqs.xls]Summary by City and Step'!B"&BV380) becomes Whoops! I got off track here. INDIRECT('[In Process Reqs.xls]Summary by City and Step'!B10) which boils down to the range '[In Process Reqs.xls]Summary by City and Step'!B10 .... just a simple reference! The beauty of it is we can change the row (or column) by inserting other formulas and wrapping it with INDIRECT. Sorry for the confusion. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup with Multiple criteria and multiple sheets | 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) | |||
multiple vlookup | Excel Worksheet Functions | |||
Multiple Vlookup? | Excel Worksheet Functions |