Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup with 4 Criteria
Hello,
I am trying to use a vlookup formula based on 4 criteria, in order to return a name value, not numeric. On one worksheet I have several interviews set up. Column A=Date, Column B=Time, Column C=Recruiter, Column D=Interview slot number (they have up to 12 people scheduled at one time). I have named this worksheet as list: INTERVIEWS. On my other worksheet, is a form. At the top, the recruiter can fill in today's date in B1, The time of their interview session in B2, and their name in B3. Below that information, starting in A8 is the Interview slot number, and then horizontally next to that are empty cells with the name of the person being interviewed (B8) , and their application number (C8), repeated 11 more times for all 12 interview slots. I'm trying to create a vlookup formula in B8 that looks up the 3 criteria filled in at the top (B1:3) as well as the interview slot# (A8) and returns what's in column 6 on my Interview tab (the person's name). I fear that this sounds immensely confusing, so if I need to attach my example I can. I've tried sumproduct formulas which only work when returning a numeric value, as well as vlookup formulas that are only returning errors. I'm not sure if this multi-criteria is possible without VBA, but am crossing my fingers!! Help! Thanks, Gina |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup with 4 Criteria
Hi Gina
One way. First create a concatenated field on Interviews, somewhere to the right of your data - I chose Column J. In J2 Enter =C2&"|"&A2&"|"&B2&"|"&D2 and copy down for the extent of your data. Next create 3 named ranges - make the first 2 Dynamic so they will grow as you add more data to sheet Interviews. InsertNameDefine Name myTable Refers to =Interviews!$A$1:INDEX(Interviews!J:J,COUNTA(Inter views!A:A)) then Name Code Refers to =Interviews!$J$1:INDEX(Interviews!J:J,COUNTA(Inter views!A:A)) then Name Headings Refers to =Interviews!$A$1:$J$1 In each case above, column I should be replaced with the last column of data on Interviews, where you have placed your concatenated formula. On Sheet2, in cells A8 through A19 you will have the numbers 1 through 12 I have assumed that in row 7, starting with B7 you will have the column headings that you wish to pick up from the Interview Sheet e.g. in B7 Name, in C7 App. No etc. In B8 Enter the following =INDEX(myTable, MATCH($B$3&"|"&$B$1&"|"&$B$2&"|"&$A8,Code,0), MATCH(B$7,Headings,0)) Copy across for as many columns as required, and copy down through B9:B19 -- Regards Roger Govier "Gina_28" <u46866@uwe wrote in message news:8b96e48a96a14@uwe... Hello, I am trying to use a vlookup formula based on 4 criteria, in order to return a name value, not numeric. On one worksheet I have several interviews set up. Column A=Date, Column B=Time, Column C=Recruiter, Column D=Interview slot number (they have up to 12 people scheduled at one time). I have named this worksheet as list: INTERVIEWS. On my other worksheet, is a form. At the top, the recruiter can fill in today's date in B1, The time of their interview session in B2, and their name in B3. Below that information, starting in A8 is the Interview slot number, and then horizontally next to that are empty cells with the name of the person being interviewed (B8) , and their application number (C8), repeated 11 more times for all 12 interview slots. I'm trying to create a vlookup formula in B8 that looks up the 3 criteria filled in at the top (B1:3) as well as the interview slot# (A8) and returns what's in column 6 on my Interview tab (the person's name). I fear that this sounds immensely confusing, so if I need to attach my example I can. I've tried sumproduct formulas which only work when returning a numeric value, as well as vlookup formulas that are only returning errors. I'm not sure if this multi-criteria is possible without VBA, but am crossing my fingers!! Help! Thanks, Gina |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup with 4 Criteria
Hello,
Only one solution that is SUMPRODUCT formula. The best way for the numberis put ISNUMBER before specified range. Let me know if you still have a doubt. Or send me your formula, i will make a correction in that. "Gina_28" wrote: Hello, I am trying to use a vlookup formula based on 4 criteria, in order to return a name value, not numeric. On one worksheet I have several interviews set up. Column A=Date, Column B=Time, Column C=Recruiter, Column D=Interview slot number (they have up to 12 people scheduled at one time). I have named this worksheet as list: INTERVIEWS. On my other worksheet, is a form. At the top, the recruiter can fill in today's date in B1, The time of their interview session in B2, and their name in B3. Below that information, starting in A8 is the Interview slot number, and then horizontally next to that are empty cells with the name of the person being interviewed (B8) , and their application number (C8), repeated 11 more times for all 12 interview slots. I'm trying to create a vlookup formula in B8 that looks up the 3 criteria filled in at the top (B1:3) as well as the interview slot# (A8) and returns what's in column 6 on my Interview tab (the person's name). I fear that this sounds immensely confusing, so if I need to attach my example I can. I've tried sumproduct formulas which only work when returning a numeric value, as well as vlookup formulas that are only returning errors. I'm not sure if this multi-criteria is possible without VBA, but am crossing my fingers!! Help! Thanks, Gina |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup with 4 Criteria
Saved from a previous post:
If you want exact matches for just two columns (and return a value from a third), you could use: =index(othersheet!$c$1:$c$100, match(1,(a2=othersheet!$a$1:$a$100) *(b2=othersheet!$b$1:$b$100),0)) (all in one cell) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can only use the whole column in xl2007. This returns the value in othersheet column C when column A and B (of othersheet) match A2 and B2 of the sheet with the formula. And you can add more conditions by just adding more stuff to that product portion of the formula: =index(othersheet!$d$1:$d$100, match(1,(a2=othersheet!$a$1:$a$100) *(b2=othersheet!$b$1:$b$100) *(c2=othersheet!$c$1:$c$100),0)) Gina_28 wrote: Hello, I am trying to use a vlookup formula based on 4 criteria, in order to return a name value, not numeric. On one worksheet I have several interviews set up. Column A=Date, Column B=Time, Column C=Recruiter, Column D=Interview slot number (they have up to 12 people scheduled at one time). I have named this worksheet as list: INTERVIEWS. On my other worksheet, is a form. At the top, the recruiter can fill in today's date in B1, The time of their interview session in B2, and their name in B3. Below that information, starting in A8 is the Interview slot number, and then horizontally next to that are empty cells with the name of the person being interviewed (B8) , and their application number (C8), repeated 11 more times for all 12 interview slots. I'm trying to create a vlookup formula in B8 that looks up the 3 criteria filled in at the top (B1:3) as well as the interview slot# (A8) and returns what's in column 6 on my Interview tab (the person's name). I fear that this sounds immensely confusing, so if I need to attach my example I can. I've tried sumproduct formulas which only work when returning a numeric value, as well as vlookup formulas that are only returning errors. I'm not sure if this multi-criteria is possible without VBA, but am crossing my fingers!! Help! Thanks, Gina -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup with 4 Criteria
Thank you soooo much!! I got everything to work perfectly!
Dave Peterson wrote: Saved from a previous post: If you want exact matches for just two columns (and return a value from a third), you could use: =index(othersheet!$c$1:$c$100, match(1,(a2=othersheet!$a$1:$a$100) *(b2=othersheet!$b$1:$b$100),0)) (all in one cell) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can only use the whole column in xl2007. This returns the value in othersheet column C when column A and B (of othersheet) match A2 and B2 of the sheet with the formula. And you can add more conditions by just adding more stuff to that product portion of the formula: =index(othersheet!$d$1:$d$100, match(1,(a2=othersheet!$a$1:$a$100) *(b2=othersheet!$b$1:$b$100) *(c2=othersheet!$c$1:$c$100),0)) Hello, [quoted text clipped - 21 lines] Thanks, Gina -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200810/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP using two criteria | Excel Worksheet Functions | |||
VLOOKUP or IF with many criteria | Excel Worksheet Functions | |||
vlookup with two criteria | Excel Worksheet Functions | |||
VLOOKUP with MAX criteria | Excel Discussion (Misc queries) | |||
vlookup with two criteria | Excel Worksheet Functions |