|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
March 16th, 2010, 07:02 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
combine vlookup and match help
I need help, and fast! I am trying to create a form, and my formula is not
working! I have been looking at thread after thread and no luck... I still get the #N/A every time. Okay, on sheet 1 (Insurance Form) is where I need my information to populate. All of my data is on sheet 2 (Look up sheet). I have a drop down to tell me my benefit choice and my coverage choice. For my cost, I want excel to look at sheet 2 and tell me what is in the cell for those two choices. Here is my data: It is looking at cell A5 for the "Benefit" (HMO, PPO,etc) and looking at B5 for the "Coverage" (EE, EE&SP, etc) on my sheet one named "Insurance Form" On my "Look Up Sheet" A B C D E 1 HMO PPO DENTAL VISION 2 EE 10.00 15.00 5.00 3.00 3 EE&SP 15.00 20.00 10.00 7.00 4 EE&CH 13.00 17.00 7.00 5.00 5 EE&FA 20.00 25.00 15.00 10.00 I have tried this formula, and others, but keep coming back with the same error every time! =VLOOKUP('Look Up Sheet'!$A$1:$E$5,2,MATCH('Insurance for LOA'!$A$5,'Look Up Sheet'!$B$2:$E$5,0)&MATCH('Insurance for LOA'!$B$5,'Look Up Sheet'!$A$2:$E$5,0)) 
|
|
#2
March 16th, 2010, 07:33 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
combine vlookup and match help
Wrong function.
=INDEX('Look Up Sheet'!$B$2:$E$5,MATCH(B5,'Look Up Sheet'!$A$2:$A$5,0),MATCH(A5,'Look Up Sheet'!$B$1:$E$1,0)) -- Best Regards, Luke M "cadustin" wrote in message ... I need help, and fast! I am trying to create a form, and my formula is not working! I have been looking at thread after thread and no luck... I still get the #N/A every time. Okay, on sheet 1 (Insurance Form) is where I need my information to populate. All of my data is on sheet 2 (Look up sheet). I have a drop down to tell me my benefit choice and my coverage choice. For my cost, I want excel to look at sheet 2 and tell me what is in the cell for those two choices. Here is my data: It is looking at cell A5 for the "Benefit" (HMO, PPO,etc) and looking at B5 for the "Coverage" (EE, EE&SP, etc) on my sheet one named "Insurance Form" On my "Look Up Sheet" A B C D E 1 HMO PPO DENTAL VISION 2 EE 10.00 15.00 5.00 3.00 3 EE&SP 15.00 20.00 10.00 7.00 4 EE&CH 13.00 17.00 7.00 5.00 5 EE&FA 20.00 25.00 15.00 10.00 I have tried this formula, and others, but keep coming back with the same error every time! =VLOOKUP('Look Up Sheet'!$A$1:$E$5,2,MATCH('Insurance for LOA'!$A$5,'Look Up Sheet'!$B$2:$E$5,0)&MATCH('Insurance for LOA'!$B$5,'Look Up Sheet'!$A$2:$E$5,0))
|
|
#3
March 16th, 2010, 08:38 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
combine vlookup and match help
I love you! I have been looking and reading every thread I can, and you did
it!!! Thank you so much Luke M!! "Luke M" wrote: Wrong function. =INDEX('Look Up Sheet'!$B$2:$E$5,MATCH(B5,'Look Up Sheet'!$A$2:$A$5,0),MATCH(A5,'Look Up Sheet'!$B$1:$E$1,0)) -- Best Regards, Luke M "cadustin" wrote in message ... I need help, and fast! I am trying to create a form, and my formula is not working! I have been looking at thread after thread and no luck... I still get the #N/A every time. Okay, on sheet 1 (Insurance Form) is where I need my information to populate. All of my data is on sheet 2 (Look up sheet). I have a drop down to tell me my benefit choice and my coverage choice. For my cost, I want excel to look at sheet 2 and tell me what is in the cell for those two choices. Here is my data: It is looking at cell A5 for the "Benefit" (HMO, PPO,etc) and looking at B5 for the "Coverage" (EE, EE&SP, etc) on my sheet one named "Insurance Form" On my "Look Up Sheet" A B C D E 1 HMO PPO DENTAL VISION 2 EE 10.00 15.00 5.00 3.00 3 EE&SP 15.00 20.00 10.00 7.00 4 EE&CH 13.00 17.00 7.00 5.00 5 EE&FA 20.00 25.00 15.00 10.00 I have tried this formula, and others, but keep coming back with the same error every time! =VLOOKUP('Look Up Sheet'!$A$1:$E$5,2,MATCH('Insurance for LOA'!$A$5,'Look Up Sheet'!$B$2:$E$5,0)&MATCH('Insurance for LOA'!$B$5,'Look Up Sheet'!$A$2:$E$5,0)) .
|
|
#4
March 16th, 2010, 08:48 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
combine vlookup and match help
Luke M, I do have one more question. As I am putting this formula in several
cells, is there a way to prevent the #N/A from appearing in the rows that do not meet the criteria? Here is an example: BENEFIT COVERAGE COST HMO Employee & Family 855.42 DENTAL Employee & Family 53.69 #N/A #N/A #N/A #N/A "Luke M" wrote: Wrong function. =INDEX('Look Up Sheet'!$B$2:$E$5,MATCH(B5,'Look Up Sheet'!$A$2:$A$5,0),MATCH(A5,'Look Up Sheet'!$B$1:$E$1,0)) -- Best Regards, Luke M "cadustin" wrote in message ... I need help, and fast! I am trying to create a form, and my formula is not working! I have been looking at thread after thread and no luck... I still get the #N/A every time. Okay, on sheet 1 (Insurance Form) is where I need my information to populate. All of my data is on sheet 2 (Look up sheet). I have a drop down to tell me my benefit choice and my coverage choice. For my cost, I want excel to look at sheet 2 and tell me what is in the cell for those two choices. Here is my data: It is looking at cell A5 for the "Benefit" (HMO, PPO,etc) and looking at B5 for the "Coverage" (EE, EE&SP, etc) on my sheet one named "Insurance Form" On my "Look Up Sheet" A B C D E 1 HMO PPO DENTAL VISION 2 EE 10.00 15.00 5.00 3.00 3 EE&SP 15.00 20.00 10.00 7.00 4 EE&CH 13.00 17.00 7.00 5.00 5 EE&FA 20.00 25.00 15.00 10.00 I have tried this formula, and others, but keep coming back with the same error every time! =VLOOKUP('Look Up Sheet'!$A$1:$E$5,2,MATCH('Insurance for LOA'!$A$5,'Look Up Sheet'!$B$2:$E$5,0)&MATCH('Insurance for LOA'!$B$5,'Look Up Sheet'!$A$2:$E$5,0)) .
|
|
#5
March 17th, 2010, 05:37 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
combine vlookup and match help
Your approach is absolutely correct; using VLOOKUP() and MATCH()
=VLOOKUP(B5,'Look Up Sheet'!$A$1:$E$5, MATCH(A5,'Look Up Sheet'!$A$1:$E$1,0),0) Now to avoid the error NA# you can use ISNA() =IF(ISNA(your formula),"",your formula) -- Jacob "cadustin" wrote: Luke M, I do have one more question. As I am putting this formula in several cells, is there a way to prevent the #N/A from appearing in the rows that do not meet the criteria? Here is an example: BENEFIT COVERAGE COST HMO Employee & Family 855.42 DENTAL Employee & Family 53.69 #N/A #N/A #N/A #N/A "Luke M" wrote: Wrong function. =INDEX('Look Up Sheet'!$B$2:$E$5,MATCH(B5,'Look Up Sheet'!$A$2:$A$5,0),MATCH(A5,'Look Up Sheet'!$B$1:$E$1,0)) -- Best Regards, Luke M "cadustin" wrote in message ... I need help, and fast! I am trying to create a form, and my formula is not working! I have been looking at thread after thread and no luck... I still get the #N/A every time. Okay, on sheet 1 (Insurance Form) is where I need my information to populate. All of my data is on sheet 2 (Look up sheet). I have a drop down to tell me my benefit choice and my coverage choice. For my cost, I want excel to look at sheet 2 and tell me what is in the cell for those two choices. Here is my data: It is looking at cell A5 for the "Benefit" (HMO, PPO,etc) and looking at B5 for the "Coverage" (EE, EE&SP, etc) on my sheet one named "Insurance Form" On my "Look Up Sheet" A B C D E 1 HMO PPO DENTAL VISION 2 EE 10.00 15.00 5.00 3.00 3 EE&SP 15.00 20.00 10.00 7.00 4 EE&CH 13.00 17.00 7.00 5.00 5 EE&FA 20.00 25.00 15.00 10.00 I have tried this formula, and others, but keep coming back with the same error every time! =VLOOKUP('Look Up Sheet'!$A$1:$E$5,2,MATCH('Insurance for LOA'!$A$5,'Look Up Sheet'!$B$2:$E$5,0)&MATCH('Insurance for LOA'!$B$5,'Look Up Sheet'!$A$2:$E$5,0)) .
|
|
#6
March 17th, 2010, 04:12 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
combine vlookup and match help
Not working Jacob, I am using the INDEX and MATCH as listed below.
"Jacob Skaria" wrote: Your approach is absolutely correct; using VLOOKUP() and MATCH() =VLOOKUP(B5,'Look Up Sheet'!$A$1:$E$5, MATCH(A5,'Look Up Sheet'!$A$1:$E$1,0),0) Now to avoid the error NA# you can use ISNA() =IF(ISNA(your formula),"",your formula) -- Jacob "cadustin" wrote: Luke M, I do have one more question. As I am putting this formula in several cells, is there a way to prevent the #N/A from appearing in the rows that do not meet the criteria? Here is an example: BENEFIT COVERAGE COST HMO Employee & Family 855.42 DENTAL Employee & Family 53.69 #N/A #N/A #N/A #N/A "Luke M" wrote: Wrong function. =INDEX('Look Up Sheet'!$B$2:$E$5,MATCH(B5,'Look Up Sheet'!$A$2:$A$5,0),MATCH(A5,'Look Up Sheet'!$B$1:$E$1,0)) -- Best Regards, Luke M "cadustin" wrote in message ... I need help, and fast! I am trying to create a form, and my formula is not working! I have been looking at thread after thread and no luck... I still get the #N/A every time. Okay, on sheet 1 (Insurance Form) is where I need my information to populate. All of my data is on sheet 2 (Look up sheet). I have a drop down to tell me my benefit choice and my coverage choice. For my cost, I want excel to look at sheet 2 and tell me what is in the cell for those two choices. Here is my data: It is looking at cell A5 for the "Benefit" (HMO, PPO,etc) and looking at B5 for the "Coverage" (EE, EE&SP, etc) on my sheet one named "Insurance Form" On my "Look Up Sheet" A B C D E 1 HMO PPO DENTAL VISION 2 EE 10.00 15.00 5.00 3.00 3 EE&SP 15.00 20.00 10.00 7.00 4 EE&CH 13.00 17.00 7.00 5.00 5 EE&FA 20.00 25.00 15.00 10.00 I have tried this formula, and others, but keep coming back with the same error every time! =VLOOKUP('Look Up Sheet'!$A$1:$E$5,2,MATCH('Insurance for LOA'!$A$5,'Look Up Sheet'!$B$2:$E$5,0)&MATCH('Insurance for LOA'!$B$5,'Look Up Sheet'!$A$2:$E$5,0)) .
|
|
#7
March 18th, 2010, 04:31 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
combine vlookup and match help
Try the below test...
Col A Col B Col C Col D X Y Z A 10 40 70 B 20 50 80 C 30 60 90 Replace strings with cell references =VLOOKUP("A",A1  4,MATCH("Y",A11,0),0)-- Jacob "cadustin" wrote: Not working Jacob, I am using the INDEX and MATCH as listed below. "Jacob Skaria" wrote: Your approach is absolutely correct; using VLOOKUP() and MATCH() =VLOOKUP(B5,'Look Up Sheet'!$A$1:$E$5, MATCH(A5,'Look Up Sheet'!$A$1:$E$1,0),0) Now to avoid the error NA# you can use ISNA() =IF(ISNA(your formula),"",your formula) -- Jacob "cadustin" wrote: Luke M, I do have one more question. As I am putting this formula in several cells, is there a way to prevent the #N/A from appearing in the rows that do not meet the criteria? Here is an example: BENEFIT COVERAGE COST HMO Employee & Family 855.42 DENTAL Employee & Family 53.69 #N/A #N/A #N/A #N/A "Luke M" wrote: Wrong function. =INDEX('Look Up Sheet'!$B$2:$E$5,MATCH(B5,'Look Up Sheet'!$A$2:$A$5,0),MATCH(A5,'Look Up Sheet'!$B$1:$E$1,0)) -- Best Regards, Luke M "cadustin" wrote in message ... I need help, and fast! I am trying to create a form, and my formula is not working! I have been looking at thread after thread and no luck... I still get the #N/A every time. Okay, on sheet 1 (Insurance Form) is where I need my information to populate. All of my data is on sheet 2 (Look up sheet). I have a drop down to tell me my benefit choice and my coverage choice. For my cost, I want excel to look at sheet 2 and tell me what is in the cell for those two choices. Here is my data: It is looking at cell A5 for the "Benefit" (HMO, PPO,etc) and looking at B5 for the "Coverage" (EE, EE&SP, etc) on my sheet one named "Insurance Form" On my "Look Up Sheet" A B C D E 1 HMO PPO DENTAL VISION 2 EE 10.00 15.00 5.00 3.00 3 EE&SP 15.00 20.00 10.00 7.00 4 EE&CH 13.00 17.00 7.00 5.00 5 EE&FA 20.00 25.00 15.00 10.00 I have tried this formula, and others, but keep coming back with the same error every time! =VLOOKUP('Look Up Sheet'!$A$1:$E$5,2,MATCH('Insurance for LOA'!$A$5,'Look Up Sheet'!$B$2:$E$5,0)&MATCH('Insurance for LOA'!$B$5,'Look Up Sheet'!$A$2:$E$5,0)) .
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
