I'm having trouble writing a formula to insert specific values from a column
that was a result of a match with a column adjacent to it.

Basically, I have three columns
-one is a list of names (AD66564..etc...etc..(machine #))
-second is a list with the values associated with these names (in this case,
the location of each machine, j5/4byprinter)
-third is another list of names (AD979879..etc..etc..)

On the forth column, I want to be able to match each name of the third column
with a name in the first column. So that if the name being tested matches the
name in the any row of the first colum, than it should display the value of
the second column that is adjacent to the first column. If not, than it
should display "no match".

I am able to use =match(C2,A:A,0) so that it displays the location of a match
with the first column. But I would rather want it to display the value of the
second column.

something like....=IF(Match(C2,A:A,0),B:B,"No Match") but this doesnt work!

Any help would be greatly appreciated :)

Noel

--
Message posted via http://www.officekb.com

Try using a VLOOKUP as follows

=IF(ISERROR(VLOOKUP(C2,$A$1:$A5000,1,FALSE)),"No
Match",VLOOKUP(C2,$A$1:$A5000,1,FALSE))

"gnotnoel via OfficeKB.com" wrote:

I'm having trouble writing a formula to insert specific values from a column
that was a result of a match with a column adjacent to it.

Basically, I have three columns
-one is a list of names (AD66564..etc...etc..(machine #))
-second is a list with the values associated with these names (in this case,
the location of each machine, j5/4byprinter)
-third is another list of names (AD979879..etc..etc..)

On the forth column, I want to be able to match each name of the third column
with a name in the first column. So that if the name being tested matches the
name in the any row of the first colum, than it should display the value of
the second column that is adjacent to the first column. If not, than it
should display "no match".

I am able to use =match(C2,A:A,0) so that it displays the location of a match
with the first column. But I would rather want it to display the value of the
second column.

something like....=IF(Match(C2,A:A,0),B:B,"No Match") but this doesnt work!

Any help would be greatly appreciated :)

Noel

--
Message posted via http://www.officekb.com

Sorry, I made a mistake. Try

=IF(ISERROR(VLOOKUP,C2,$A$1:$A$500,1,FALSE)),"No Match",B2)

"Magnivy" wrote:

Try using a VLOOKUP as follows

=IF(ISERROR(VLOOKUP(C2,$A$1:$A5000,1,FALSE)),"No
Match",VLOOKUP(C2,$A$1:$A5000,1,FALSE))

"gnotnoel via OfficeKB.com" wrote:

I'm having trouble writing a formula to insert specific values from a column
that was a result of a match with a column adjacent to it.

Basically, I have three columns
-one is a list of names (AD66564..etc...etc..(machine #))
-second is a list with the values associated with these names (in this case,
the location of each machine, j5/4byprinter)
-third is another list of names (AD979879..etc..etc..)

On the forth column, I want to be able to match each name of the third column
with a name in the first column. So that if the name being tested matches the
name in the any row of the first colum, than it should display the value of
the second column that is adjacent to the first column. If not, than it
should display "no match".

I am able to use =match(C2,A:A,0) so that it displays the location of a match
with the first column. But I would rather want it to display the value of the
second column.

something like....=IF(Match(C2,A:A,0),B:B,"No Match") but this doesnt work!

Any help would be greatly appreciated :)

Noel

--
Message posted via http://www.officekb.com

maybe...
=if(isnumber(match(c2,A:a,0)),index(b:b,match(c2,a :a,0)),"No Match")
or
=IF(iserror(match(C2,a:a,0)),"no match",index(b:b,match(C2,A:A,0)))
or
=if(iserror(vlookup(c2,a:b,2,false)),"no match",vlookup(c2,a:b,2,false))

You may want to read Debra Dalgleish's notes:
http://www.contextures.com/xlFunctions02.html (for =vlookup())
and
http://www.contextures.com/xlFunctions03.html (for =index(match()))



"gnotnoel via OfficeKB.com" wrote:

I'm having trouble writing a formula to insert specific values from a column
that was a result of a match with a column adjacent to it.

Basically, I have three columns
-one is a list of names (AD66564..etc...etc..(machine #))
-second is a list with the values associated with these names (in this case,
the location of each machine, j5/4byprinter)
-third is another list of names (AD979879..etc..etc..)

On the forth column, I want to be able to match each name of the third column
with a name in the first column. So that if the name being tested matches the
name in the any row of the first colum, than it should display the value of
the second column that is adjacent to the first column. If not, than it
should display "no match".

I am able to use =match(C2,A:A,0) so that it displays the location of a match
with the first column. But I would rather want it to display the value of the
second column.

something like....=IF(Match(C2,A:A,0),B:B,"No Match") but this doesnt work!

Any help would be greatly appreciated :)

Noel

--
Message posted via http://www.officekb.com

--

Dave Peterson

Thanks guys, it looks =if(isnumber(match(c2,A:a,0)),index(b:b,match(c2,a :a,0))
,"No Match")
worked the best for me.

Worked like wonders!

Noel

Dave Peterson wrote:
maybe...
=if(isnumber(match(c2,A:a,0)),index(b:b,match(c2, a:a,0)),"No Match")
or
=IF(iserror(match(C2,a:a,0)),"no match",index(b:b,match(C2,A:A,0)))
or
=if(iserror(vlookup(c2,a:b,2,false)),"no match",vlookup(c2,a:b,2,false))

You may want to read Debra Dalgleish's notes:
http://www.contextures.com/xlFunctions02.html (for =vlookup())
and
http://www.contextures.com/xlFunctions03.html (for =index(match()))

I'm having trouble writing a formula to insert specific values from a column
that was a result of a match with a column adjacent to it.
[quoted text clipped - 20 lines]

Noel


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200606/1