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
|
|||
|
|||
Help needed for Excel formula using if, iserr, match and vlookup!
Hi
I need help with the following formula: =if(=iserr(=match(a2,LIST,0))=0,=vlookup(a2,DATA,1 ),0) Excel says there is something wrong with my formula but won't say what. What is it doing? I have a list of data in columns B, C and D that are unique to the value in A. Basically on Sheet 2 cell B2 I want to lookup a value (in A2) from the selected range (LIST on Sheet1 ie: A2:A100) and if it is there, then I want the cell (B2) to return the corresponding value from column 1 in the range DATA (A2100). if it is not there then I want it to return a value of n/a or "#n/a" (anything to indicate it isn't there). I have moved over from Lotus 123 (cause I found it difficult to use) and the formula I use (sucessfully) in 123 is: @if(@iserr(@match(a2,$list,0))=0,@vlookup(a2,$data ,1),0) |
#2
|
|||
|
|||
Help needed for Excel formula using if, iserr, match and vlookup!
Try it like this:
=if(iserror(match(a2,LIST,0)),"n/a",vlookup(a2,DATA,1,0)) I assume that LIST and DATA are named ranges that you have defined. If you haven't then you will have to put the range references in explicitly like this: =if(iserror(match(a2,Sheet1!$A$2:$A$100,0)),"n/a",vlookup(a2,Sheet1!$A $2,$D$100,1,0)) You might want to change the column number for the returned data - all this is doing (with column number of 1) is returning the same value that you have just looked up, and there is little point in defining a table of 4 columns in your formula if you are only using the first one. Hope this helps. Pete On Feb 12, 9:48*am, midget wrote: Hi I need help with the following formula: =if(=iserr(=match(a2,LIST,0))=0,=vlookup(a2,DATA,1 ),0) Excel says there is something wrong with my formula but won't say what. What is it doing? I have a list of data in columns B, C and D that are unique to the value in A. Basically on Sheet 2 cell B2 I want to lookup a value (in A2) from the selected range (LIST on Sheet1 ie: A2:A100) and if it is there, then I want the cell (B2) to return the corresponding value from column 1 in the range DATA (A2100). if it is not there then I want it to return a value of n/a or "#n/a" (anything to indicate it isn't there). I have moved over from Lotus 123 (cause I found it difficult to use) and the formula I use (sucessfully) in 123 is: @if(@iserr(@match(a2,$list,0))=0,@vlookup(a2,$data ,1),0) |
Thread Tools | |
Display Modes | |
|
|