| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP - returning the searched for value if it does not exist
I am searching for a value from one table to another (the value is located in
Column A in Sheet2 and column H in Sheet1). The formula seen below is what I have been using (in Sheet3) =IF(ISNA(VLOOKUP(Sheet2!A2,Sheet1!$H$2:$H$9000,1,F ALSE)),IF(BTypesInputTable!A2 = "","",BTypesInputTable!A2),"") the IF statement will convert a null value to a blank space instead of a zero. But, if the value that im searching for is deleted in Sheet2, i get an #N/A. Shouldnt this formula just skip a deleted row and go to the next? (the row is completely deleted....it is not a blank row) here is what the formula looks like after i try to search for that missing value: =IF(ISNA(VLOOKUP(Sheet2!#REF!,Sheet1!$H$2:$H$9000, 1,FALSE)),IF(Sheet2!#REF! = "", "", Sheet2!#REF!),"") excel places that #REF! in my formula if the value USED to exist and then does not anymore. any ideas on how to fix this? thanks in advance! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLOOKUP - returning the searched for value if it does not exist
Nel post
*njuneardave* ha scritto: I am searching for a value from one table to another (the value is located in Column A in Sheet2 and column H in Sheet1). The formula seen below is what I have been using (in Sheet3) =IF(ISNA(VLOOKUP(Sheet2!A2,Sheet1!$H$2:$H$9000,1,F ALSE)),IF(BTypesInputTable!A2 = "","",BTypesInputTable!A2),"") the IF statement will convert a null value to a blank space instead of a zero. But, if the value that im searching for is deleted in Sheet2, i get an #N/A. Shouldnt this formula just skip a deleted row and go to the next? (the row is completely deleted....it is not a blank row) Such a beaviour is due because the value you are looking for is not in the same sheet af the formula. To skip the problem you can use the INDIRECT function: =IF(ISNA(VLOOKUP(INDIRECT("Sheet2!A2"),Sheet1!$H$2 :$H$9000,1,FALSE)),IF(BTypesInputTable!A2= "","",BTypesInputTable!A2),"") -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLOOKUP - returning the searched for value if it does not exis
Wow, thanks Franz, that worked great. Now a follow-up question: I am having
the same problem using INDEX and MATCH....here is what I have: =IF(INDEX(Sheet2!$B$2:$B$9000,MATCH(1,(Sheet1!$A$2 :$A$9000=Sheet2!$A2)*(Sheet1!$E$2:$E$9000=Sheet2!$ E2),0)) < INDEX(Sheet1!$B$2:$B$9000,MATCH(1,(Sheet2!$A$2:$A$ 9000=Sheet1!$A2)*(Sheet2!$E$2:$E$9000=Sheet1!$E2), 0)), INDEX(Sheet2!$B$2:$B$9000,MATCH(1,(Sheet1!$A$2:$A$ 9000=Sheet2!$A2)*(Sheet1!$E$2:$E$9000=Sheet2!$E2), 0)), "") I am trying to see if the value in sheet1 and sheet2 that are respective to their identifiers (in columns A and E) are equal. If they are equal, i dont show anything, but if they are different, I want to show the value in Sheet2. Is this formula way over complicated? Also, can i use indirect on this formula to get rid of the #N/A references? Thanks in advance! "Franz Verga" wrote: Nel post *njuneardave* ha scritto: I am searching for a value from one table to another (the value is located in Column A in Sheet2 and column H in Sheet1). The formula seen below is what I have been using (in Sheet3) =IF(ISNA(VLOOKUP(Sheet2!A2,Sheet1!$H$2:$H$9000,1,F ALSE)),IF(BTypesInputTable!A2 = "","",BTypesInputTable!A2),"") the IF statement will convert a null value to a blank space instead of a zero. But, if the value that im searching for is deleted in Sheet2, i get an #N/A. Shouldnt this formula just skip a deleted row and go to the next? (the row is completely deleted....it is not a blank row) Such a beaviour is due because the value you are looking for is not in the same sheet af the formula. To skip the problem you can use the INDIRECT function: =IF(ISNA(VLOOKUP(INDIRECT("Sheet2!A2"),Sheet1!$H$2 :$H$9000,1,FALSE)),IF(BTypesInputTable!A2= "","",BTypesInputTable!A2),"") -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLOOKUP - returning the searched for value if it does not exis
Nel post
*njuneardave* ha scritto: Wow, thanks Franz, that worked great. Now a follow-up question: I am having the same problem using INDEX and MATCH....here is what I have: =IF(INDEX(Sheet2!$B$2:$B$9000,MATCH(1,(Sheet1!$A$2 :$A$9000=Sheet2!$A2)*(Sheet1!$E$2:$E$9000=Sheet2!$ E2),0)) < INDEX(Sheet1!$B$2:$B$9000,MATCH(1,(Sheet2!$A$2:$A$ 9000=Sheet1!$A2)*(Sheet2!$E$2:$E$9000=Sheet1!$E2), 0)), INDEX(Sheet2!$B$2:$B$9000,MATCH(1,(Sheet1!$A$2:$A$ 9000=Sheet2!$A2)*(Sheet1!$E$2:$E$9000=Sheet2!$E2), 0)), "") I am trying to see if the value in sheet1 and sheet2 that are respective to their identifiers (in columns A and E) are equal. If they are equal, i dont show anything, but if they are different, I want to show the value in Sheet2. Is this formula way over complicated? Also, can i use indirect on this formula to get rid of the #N/A references? Yes, in the same manner I showed you in my last post... Thanks in advance! You're welcome (in advance...) -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLOOKUP - returning the searched for value if it does not exis
Franz, I tried what you said.....this is what I put:
=IF(ISERROR(INDEX(Sheet2!B$2:B$9000,MATCH(1,(Sheet 1!$A$2:$A$9000=Sheet2!$A2)*(Sheet1!$E$2:$E$9000=Sh eet2!$E2),0))), "", IF(INDEX(Sheet2!B$2:B$9000,MATCH(1,(Sheet1!$A$2:$A $9000=Sheet2!$A2)*(Sheet1!$E$2:$E$9000=Sheet2!$E2) ,0)) = INDEX(Sheet1!B$2:B$9000,MATCH(1,(Sheet2!$A$2:$A$90 00=Sheet1!$A2)*(Sheet2!$E$2:$E$9000=Sheet1!$E2),0) ), "", INDEX(Sheet2!B$2:B$9000,MATCH(1,(Sheet1!$A$2:$A$90 00=Sheet2!$A2)*(Sheet1!$E$2:$E$9000=Sheet2!$E2),0) ))) for some, it returns an #N/A. for others it returns a number....but the index is off... i want to either return the value at A2 for Sheet2 or nothing at all. What is wrong with this? Thanks alot, i really appreciate the help "Franz Verga" wrote: Nel post *njuneardave* ha scritto: Wow, thanks Franz, that worked great. Now a follow-up question: I am having the same problem using INDEX and MATCH....here is what I have: =IF(INDEX(Sheet2!$B$2:$B$9000,MATCH(1,(Sheet1!$A$2 :$A$9000=Sheet2!$A2)*(Sheet1!$E$2:$E$9000=Sheet2!$ E2),0)) < INDEX(Sheet1!$B$2:$B$9000,MATCH(1,(Sheet2!$A$2:$A$ 9000=Sheet1!$A2)*(Sheet2!$E$2:$E$9000=Sheet1!$E2), 0)), INDEX(Sheet2!$B$2:$B$9000,MATCH(1,(Sheet1!$A$2:$A$ 9000=Sheet2!$A2)*(Sheet1!$E$2:$E$9000=Sheet2!$E2), 0)), "") I am trying to see if the value in sheet1 and sheet2 that are respective to their identifiers (in columns A and E) are equal. If they are equal, i dont show anything, but if they are different, I want to show the value in Sheet2. Is this formula way over complicated? Also, can i use indirect on this formula to get rid of the #N/A references? Yes, in the same manner I showed you in my last post... Thanks in advance! You're welcome (in advance...) -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLOOKUP - returning the searched for value if it does not exis
Nel post
*njuneardave* ha scritto: Franz, I tried what you said.....this is what I put: =IF(ISERROR(INDEX(Sheet2!B$2:B$9000,MATCH(1,(Sheet 1!$A$2:$A$9000=Sheet2!$A2)*(Sheet1!$E$2:$E$9000=Sh eet2!$E2),0))), "", IF(INDEX(Sheet2!B$2:B$9000,MATCH(1,(Sheet1!$A$2:$A $9000=Sheet2!$A2)*(Sheet1!$E$2:$E$9000=Sheet2!$E2) ,0)) = INDEX(Sheet1!B$2:B$9000,MATCH(1,(Sheet2!$A$2:$A$90 00=Sheet1!$A2)*(Sheet2!$E$2:$E$9000=Sheet1!$E2),0) ), "", INDEX(Sheet2!B$2:B$9000,MATCH(1,(Sheet1!$A$2:$A$90 00=Sheet2!$A2)*(Sheet1!$E$2:$E$9000=Sheet2!$E2),0) ))) for some, it returns an #N/A. for others it returns a number....but the index is off... i want to either return the value at A2 for Sheet2 or nothing at all. What is wrong with this? Here it seems you don't use the INDIRECT function... So obviously it returns #N/A. You have to "incapsulate" your references inside the INDIRECT function, when you think you could delete some row; this is an example: INDIRECT("your reference") so the first INDEX MATCH of your formula becomes: INDEX(INDIRECT("Sheet2!B$2:B$9000"),MATCH(1,(Sheet 1!$A$2:$A$9000=INDIRECT("Sheet2!$A2"))*(Sheet1!$E$ 2:$E$9000=INDIRECT("Sheet2!$E2")),0)) -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLOOKUP - returning the searched for value if it does not exist
Franz,
I finally got it to work. Thank you for your help. You are excellent! "njuneardave" wrote: I am searching for a value from one table to another (the value is located in Column A in Sheet2 and column H in Sheet1). The formula seen below is what I have been using (in Sheet3) =IF(ISNA(VLOOKUP(Sheet2!A2,Sheet1!$H$2:$H$9000,1,F ALSE)),IF(BTypesInputTable!A2 = "","",BTypesInputTable!A2),"") the IF statement will convert a null value to a blank space instead of a zero. But, if the value that im searching for is deleted in Sheet2, i get an #N/A. Shouldnt this formula just skip a deleted row and go to the next? (the row is completely deleted....it is not a blank row) here is what the formula looks like after i try to search for that missing value: =IF(ISNA(VLOOKUP(Sheet2!#REF!,Sheet1!$H$2:$H$9000, 1,FALSE)),IF(Sheet2!#REF! = "", "", Sheet2!#REF!),"") excel places that #REF! in my formula if the value USED to exist and then does not anymore. any ideas on how to fix this? thanks in advance! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VLOOKUP not returning results | Excel Worksheet Functions | |||
| vlookup returning a #N/A value | Excel Worksheet Functions | |||
| need help with a vlookup but returning a particular match? | Excel Worksheet Functions | |||
| Vlookup returning #N/A | Excel Worksheet Functions | |||
| MY VLOOKUP IS RETURNING N/A AND THEREFORE MY TOTAL SUM AT THE BOT. | Excel Discussion (Misc queries) | |||
Hybrid Mode
