| Home |
| Search |
| Today's Posts |
|
|
|
#1
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
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
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
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
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
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
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:D4,MATCH("Y",A1:D1,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)) . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Match and combine multiple rows into one | Excel Worksheet Functions | |||
| Combine HLOOKUP and MATCH | Excel Worksheet Functions | |||
| Excel: Combine and match two data | Excel Discussion (Misc queries) | |||
| Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
| How do I combine and match data from 2 sheets | Excel Discussion (Misc queries) | |||
Hybrid Mode
