| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
How to overcome LOOKUP function problems?
Hello,
I am trying to match data from 2 worksheets using LOOKUP functions. I have arranged the data in ascending order. Here is my problem: Entry 1, 2, 3 shown up as 'n/a#' which is fine because the are supposed to be 'no match'. Entry 7, 8 , 9 are supposed to show 'no match' but returned with the data in entry 6. Which means LOOKUP function isn't working for me. I tried VLOOKUP, it just picks up the first avaliable data in the worksheet. But I need to last available data. That is no good to me either. I thought about incorporating 'IF' funcation with the 'VLOOKUP' function to get the results I want. Unfortunately, I am not that good with Excel. Can anyone help me? Thanks, Wendy |
|
#2
|
|||
|
|||
|
Care to post the formula you tried?
Wendy wrote: Hello, I am trying to match data from 2 worksheets using LOOKUP functions. I have arranged the data in ascending order. Here is my problem: Entry 1, 2, 3 shown up as 'n/a#' which is fine because the are supposed to be 'no match'. Entry 7, 8 , 9 are supposed to show 'no match' but returned with the data in entry 6. Which means LOOKUP function isn't working for me. I tried VLOOKUP, it just picks up the first avaliable data in the worksheet. But I need to last available data. That is no good to me either. I thought about incorporating 'IF' funcation with the 'VLOOKUP' function to get the results I want. Unfortunately, I am not that good with Excel. Can anyone help me? Thanks, Wendy |
|
#3
|
|||
|
|||
|
Hello Aladin,
I want to match data from worksheet 1 and 2, and return the data in worksheet 1 B1 to B4. Here are the info: Worksheet 1 Column A contains product code A1: 0 A2: 1 A3: 2 A4: 3 Worsheet 2 Column A contains product codes and column B contains price A1: 1 ; B1: $25 A2: 3 ; B2: $20 A3: 4 ; B3: $19 A4: 5 ; B4: $22 I want to match age to name in worksheet 1. Here is the formula: =LOOKUP(A1,'worksheet2A1:A4', 'worksheet2B1:B4') Here are my response: B1 = N/A# B2 = $25 B3 = $25 B4 = $20 As you can see, B3 should be blank as it doesn't appear in worksheet 2. How can I make it to show the correct data? Thanks for your help. Cheers, Wendy "Aladin Akyurek" wrote: Care to post the formula you tried? Wendy wrote: Hello, I am trying to match data from 2 worksheets using LOOKUP functions. I have arranged the data in ascending order. Here is my problem: Entry 1, 2, 3 shown up as 'n/a#' which is fine because the are supposed to be 'no match'. Entry 7, 8 , 9 are supposed to show 'no match' but returned with the data in entry 6. Which means LOOKUP function isn't working for me. I tried VLOOKUP, it just picks up the first avaliable data in the worksheet. But I need to last available data. That is no good to me either. I thought about incorporating 'IF' funcation with the 'VLOOKUP' function to get the results I want. Unfortunately, I am not that good with Excel. Can anyone help me? Thanks, Wendy |
|
#4
|
|||
|
|||
|
Try:
=IF(ISNA(vlookup(A1,worksheet2!$A$1:$B$4,2,FALSE)) ,"Not Found", vlookup(A1,worksheet2!$A$1:$B$4,2,FALSE)) "Wendy" wrote: Hello Aladin, I want to match data from worksheet 1 and 2, and return the data in worksheet 1 B1 to B4. Here are the info: Worksheet 1 Column A contains product code A1: 0 A2: 1 A3: 2 A4: 3 Worsheet 2 Column A contains product codes and column B contains price A1: 1 ; B1: $25 A2: 3 ; B2: $20 A3: 4 ; B3: $19 A4: 5 ; B4: $22 I want to match age to name in worksheet 1. Here is the formula: =LOOKUP(A1,'worksheet2A1:A4', 'worksheet2B1:B4') Here are my response: B1 = N/A# B2 = $25 B3 = $25 B4 = $20 As you can see, B3 should be blank as it doesn't appear in worksheet 2. How can I make it to show the correct data? Thanks for your help. Cheers, Wendy "Aladin Akyurek" wrote: Care to post the formula you tried? Wendy wrote: Hello, I am trying to match data from 2 worksheets using LOOKUP functions. I have arranged the data in ascending order. Here is my problem: Entry 1, 2, 3 shown up as 'n/a#' which is fine because the are supposed to be 'no match'. Entry 7, 8 , 9 are supposed to show 'no match' but returned with the data in entry 6. Which means LOOKUP function isn't working for me. I tried VLOOKUP, it just picks up the first avaliable data in the worksheet. But I need to last available data. That is no good to me either. I thought about incorporating 'IF' funcation with the 'VLOOKUP' function to get the results I want. Unfortunately, I am not that good with Excel. Can anyone help me? Thanks, Wendy |
|
#5
|
|||
|
|||
|
Two options...
(a) Since A1:B4 on worksheet2 is sorted in ascending order on column A, a lookup formula can be invoked if the sort order can be kept in ascending order: In B1 on worksheet1 enter & copy down: =IF(A1=worksheet2!$A$1,F(LOOKUP(A1,worksheet2!$A$ 1:$A$4)=A1,LOOKUP(A1,worksheet2!$A$1:$B$4),""),"") (b) =IF(ISNUMBER(MATCH(A1,worksheet2!$A$1:$A$4,0)),VLO OKUP(A1,worksheet2!$A$1:$B$4,2,0),"") Wendy wrote: Hello Aladin, I want to match data from worksheet 1 and 2, and return the data in worksheet 1 B1 to B4. Here are the info: Worksheet 1 Column A contains product code A1: 0 A2: 1 A3: 2 A4: 3 Worsheet 2 Column A contains product codes and column B contains price A1: 1 ; B1: $25 A2: 3 ; B2: $20 A3: 4 ; B3: $19 A4: 5 ; B4: $22 I want to match age to name in worksheet 1. Here is the formula: =LOOKUP(A1,'worksheet2A1:A4', 'worksheet2B1:B4') Here are my response: B1 = N/A# B2 = $25 B3 = $25 B4 = $20 As you can see, B3 should be blank as it doesn't appear in worksheet 2. How can I make it to show the correct data? Thanks for your help. Cheers, Wendy "Aladin Akyurek" wrote: Care to post the formula you tried? Wendy wrote: Hello, I am trying to match data from 2 worksheets using LOOKUP functions. I have arranged the data in ascending order. Here is my problem: Entry 1, 2, 3 shown up as 'n/a#' which is fine because the are supposed to be 'no match'. Entry 7, 8 , 9 are supposed to show 'no match' but returned with the data in entry 6. Which means LOOKUP function isn't working for me. I tried VLOOKUP, it just picks up the first avaliable data in the worksheet. But I need to last available data. That is no good to me either. I thought about incorporating 'IF' funcation with the 'VLOOKUP' function to get the results I want. Unfortunately, I am not that good with Excel. Can anyone help me? Thanks, Wendy -- [1] The SumProduct function should implicitly coerce the truth values to their Excel numeric equivalents. [2] The lookup functions should have an optional argument for the return value, defaulting to #N/A in its absence. |
|
#6
|
|||
|
|||
|
Hello,
Thanks for your help. I tried all the options. They work fine. Now I want to alter worksheet 2 and include a total of the product price. I want to have B1 on worksheet 1 to pick up the sums. Worksheet 1 Column A contains product code A1: 0 A2: 1 A3: 2 A4: 3 Worsheet 2 Column A contains product codes and column B contains price A1: 1 ; B1: $25 A2: 1 ; B2: $20 A3: 1 ; B3: $45 A3: 2 ; B3: $19 A4: 2 ; B4: $22 A5: 2 ; B5: $41 I want B1 on worksheet 1 to show the total of product '1', ie, $45. And B3 to show the show the total of $41. Help appreciated. Cheers, Wendy "Duke Carey" wrote: Try: =IF(ISNA(vlookup(A1,worksheet2!$A$1:$B$4,2,FALSE)) ,"Not Found", vlookup(A1,worksheet2!$A$1:$B$4,2,FALSE)) "Wendy" wrote: Hello Aladin, I want to match data from worksheet 1 and 2, and return the data in worksheet 1 B1 to B4. Here are the info: Worksheet 1 Column A contains product code A1: 0 A2: 1 A3: 2 A4: 3 Worsheet 2 Column A contains product codes and column B contains price A1: 1 ; B1: $25 A2: 3 ; B2: $20 A3: 4 ; B3: $19 A4: 5 ; B4: $22 I want to match age to name in worksheet 1. Here is the formula: =LOOKUP(A1,'worksheet2A1:A4', 'worksheet2B1:B4') Here are my response: B1 = N/A# B2 = $25 B3 = $25 B4 = $20 As you can see, B3 should be blank as it doesn't appear in worksheet 2. How can I make it to show the correct data? Thanks for your help. Cheers, Wendy "Aladin Akyurek" wrote: Care to post the formula you tried? Wendy wrote: Hello, I am trying to match data from 2 worksheets using LOOKUP functions. I have arranged the data in ascending order. Here is my problem: Entry 1, 2, 3 shown up as 'n/a#' which is fine because the are supposed to be 'no match'. Entry 7, 8 , 9 are supposed to show 'no match' but returned with the data in entry 6. Which means LOOKUP function isn't working for me. I tried VLOOKUP, it just picks up the first avaliable data in the worksheet. But I need to last available data. That is no good to me either. I thought about incorporating 'IF' funcation with the 'VLOOKUP' function to get the results I want. Unfortunately, I am not that good with Excel. Can anyone help me? Thanks, Wendy |
|
#7
|
|||
|
|||
|
Hello Aladin,
Thanks for your help. But I have changed worksheet 1 and 2 a bit to include a 'sum' of the product prices. The formula you gave me is no longer valid as I want to pick up the sum's as oppose to the first matching entry on worksheet 2. Please take a look at the below. Thanks for your help. Cheers, Wendy "Aladin Akyurek" wrote: Two options... (a) Since A1:B4 on worksheet2 is sorted in ascending order on column A, a lookup formula can be invoked if the sort order can be kept in ascending order: In B1 on worksheet1 enter & copy down: =IF(A1=worksheet2!$A$1,F(LOOKUP(A1,worksheet2!$A$ 1:$A$4)=A1,LOOKUP(A1,worksheet2!$A$1:$B$4),""),"") (b) =IF(ISNUMBER(MATCH(A1,worksheet2!$A$1:$A$4,0)),VLO OKUP(A1,worksheet2!$A$1:$B$4,2,0),"") Wendy wrote: Hello Aladin, I want to match data from worksheet 1 and 2, and return the data in worksheet 1 B1 to B4. Here are the info: Worksheet 1 Column A contains product code A1: 0 A2: 1 A3: 2 A4: 3 Worsheet 2 Column A contains product codes and column B contains price A1: 1 ; B1: $25 A2: 3 ; B2: $20 A3: 4 ; B3: $19 A4: 5 ; B4: $22 I want to match age to name in worksheet 1. Here is the formula: =LOOKUP(A1,'worksheet2A1:A4', 'worksheet2B1:B4') Here are my response: B1 = N/A# B2 = $25 B3 = $25 B4 = $20 As you can see, B3 should be blank as it doesn't appear in worksheet 2. How can I make it to show the correct data? Thanks for your help. Cheers, Wendy "Aladin Akyurek" wrote: Care to post the formula you tried? Wendy wrote: Hello, I am trying to match data from 2 worksheets using LOOKUP functions. I have arranged the data in ascending order. Here is my problem: Entry 1, 2, 3 shown up as 'n/a#' which is fine because the are supposed to be 'no match'. Entry 7, 8 , 9 are supposed to show 'no match' but returned with the data in entry 6. Which means LOOKUP function isn't working for me. I tried VLOOKUP, it just picks up the first avaliable data in the worksheet. But I need to last available data. That is no good to me either. I thought about incorporating 'IF' funcation with the 'VLOOKUP' function to get the results I want. Unfortunately, I am not that good with Excel. Can anyone help me? Thanks, Wendy -- [1] The SumProduct function should implicitly coerce the truth values to their Excel numeric equivalents. [2] The lookup functions should have an optional argument for the return value, defaulting to #N/A in its absence. |
|
#8
|
|||
|
|||
|
Try a SumIf formula.
Wendy wrote: Hello Aladin, Thanks for your help. But I have changed worksheet 1 and 2 a bit to include a 'sum' of the product prices. The formula you gave me is no longer valid as I want to pick up the sum's as oppose to the first matching entry on worksheet 2. Please take a look at the below. Thanks for your help. Cheers, Wendy "Aladin Akyurek" wrote: Two options... (a) Since A1:B4 on worksheet2 is sorted in ascending order on column A, a lookup formula can be invoked if the sort order can be kept in ascending order: In B1 on worksheet1 enter & copy down: =IF(A1=worksheet2!$A$1,F(LOOKUP(A1,worksheet2!$ A$1:$A$4)=A1,LOOKUP(A1,worksheet2!$A$1:$B$4),"")," ") (b) =IF(ISNUMBER(MATCH(A1,worksheet2!$A$1:$A$4,0)),V LOOKUP(A1,worksheet2!$A$1:$B$4,2,0),"") Wendy wrote: Hello Aladin, I want to match data from worksheet 1 and 2, and return the data in worksheet 1 B1 to B4. Here are the info: Worksheet 1 Column A contains product code A1: 0 A2: 1 A3: 2 A4: 3 Worsheet 2 Column A contains product codes and column B contains price A1: 1 ; B1: $25 A2: 3 ; B2: $20 A3: 4 ; B3: $19 A4: 5 ; B4: $22 I want to match age to name in worksheet 1. Here is the formula: =LOOKUP(A1,'worksheet2A1:A4', 'worksheet2B1:B4') Here are my response: B1 = N/A# B2 = $25 B3 = $25 B4 = $20 As you can see, B3 should be blank as it doesn't appear in worksheet 2. How can I make it to show the correct data? Thanks for your help. Cheers, Wendy "Aladin Akyurek" wrote: Care to post the formula you tried? Wendy wrote: Hello, I am trying to match data from 2 worksheets using LOOKUP functions. I have arranged the data in ascending order. Here is my problem: Entry 1, 2, 3 shown up as 'n/a#' which is fine because the are supposed to be 'no match'. Entry 7, 8 , 9 are supposed to show 'no match' but returned with the data in entry 6. Which means LOOKUP function isn't working for me. I tried VLOOKUP, it just picks up the first avaliable data in the worksheet. But I need to last available data. That is no good to me either. I thought about incorporating 'IF' funcation with the 'VLOOKUP' function to get the results I want. Unfortunately, I am not that good with Excel. Can anyone help me? Thanks, Wendy -- [1] The SumProduct function should implicitly coerce the truth values to their Excel numeric equivalents. [2] The lookup functions should have an optional argument for the return value, defaulting to #N/A in its absence. -- [1] The SumProduct function should implicitly coerce the truth values to their Excel numeric equivalents. [2] The lookup functions should have an optional argument for the return value, defaulting to #N/A in its absence. |
|
#9
|
|||
|
|||
|
Thanks, Aladin. It works fine now. FYI, here is the end result.
=IF(ISNA(VLOOKUP(E2,'2005-7 Invoice'!$A$1:$B$138,2,FALSE)),"Not Found",SUMIF('2005-7 Invoice'!$A$2:$A$136,'2005-7 PO'!E2,'2005-7 Invoice'!$B$2:$B$136)) "Aladin Akyurek" wrote: Try a SumIf formula. Wendy wrote: Hello Aladin, Thanks for your help. But I have changed worksheet 1 and 2 a bit to include a 'sum' of the product prices. The formula you gave me is no longer valid as I want to pick up the sum's as oppose to the first matching entry on worksheet 2. Please take a look at the below. Thanks for your help. Cheers, Wendy "Aladin Akyurek" wrote: Two options... (a) Since A1:B4 on worksheet2 is sorted in ascending order on column A, a lookup formula can be invoked if the sort order can be kept in ascending order: In B1 on worksheet1 enter & copy down: =IF(A1=worksheet2!$A$1,F(LOOKUP(A1,worksheet2!$ A$1:$A$4)=A1,LOOKUP(A1,worksheet2!$A$1:$B$4),"")," ") (b) =IF(ISNUMBER(MATCH(A1,worksheet2!$A$1:$A$4,0)),V LOOKUP(A1,worksheet2!$A$1:$B$4,2,0),"") Wendy wrote: Hello Aladin, I want to match data from worksheet 1 and 2, and return the data in worksheet 1 B1 to B4. Here are the info: Worksheet 1 Column A contains product code A1: 0 A2: 1 A3: 2 A4: 3 Worsheet 2 Column A contains product codes and column B contains price A1: 1 ; B1: $25 A2: 3 ; B2: $20 A3: 4 ; B3: $19 A4: 5 ; B4: $22 I want to match age to name in worksheet 1. Here is the formula: =LOOKUP(A1,'worksheet2A1:A4', 'worksheet2B1:B4') Here are my response: B1 = N/A# B2 = $25 B3 = $25 B4 = $20 As you can see, B3 should be blank as it doesn't appear in worksheet 2. How can I make it to show the correct data? Thanks for your help. Cheers, Wendy "Aladin Akyurek" wrote: Care to post the formula you tried? Wendy wrote: Hello, I am trying to match data from 2 worksheets using LOOKUP functions. I have arranged the data in ascending order. Here is my problem: Entry 1, 2, 3 shown up as 'n/a#' which is fine because the are supposed to be 'no match'. Entry 7, 8 , 9 are supposed to show 'no match' but returned with the data in entry 6. Which means LOOKUP function isn't working for me. I tried VLOOKUP, it just picks up the first avaliable data in the worksheet. But I need to last available data. That is no good to me either. I thought about incorporating 'IF' funcation with the 'VLOOKUP' function to get the results I want. Unfortunately, I am not that good with Excel. Can anyone help me? Thanks, Wendy -- [1] The SumProduct function should implicitly coerce the truth values to their Excel numeric equivalents. [2] The lookup functions should have an optional argument for the return value, defaulting to #N/A in its absence. -- [1] The SumProduct function should implicitly coerce the truth values to their Excel numeric equivalents. [2] The lookup functions should have an optional argument for the return value, defaulting to #N/A in its absence. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Replacing #N/A response when obtained in Lookup function | Excel Worksheet Functions | |||
| Lookup function problem (kg) | Excel Worksheet Functions | |||
| Lookup function skipping columns | Excel Worksheet Functions | |||
| How do I use 3 cells to create the string for a lookup function? | Excel Worksheet Functions | |||
| Lookup function w/Text and Year | Excel Worksheet Functions | |||
Linear Mode
