posting values

on excel,i've been using v-lookup to get values in the same row from one
column but i was wondering if it was possible to use data in 2 columns and
get a value in the same row to transfer into my data sheet. I looked at
every function in the program but can't seem to find the way to do that. if
anyone knows how to do this, pls respond to this. thanks-lisa

Lisa,

VLOOKUP can match a value in the first column and return the value from any
other column - and the same row as the matched value - just expand the
second range to include the data that you want to have returned, and use the
third parameter to specify the column number. For example, to match a value
in column A and return the value from column C, along the lines of

=VLOOKUP("Lisa",A1:C10,3,FALSE)

The looked-up value can also be a cell reference:
=VLOOKUP(F1,A1:C10,3,FALSE)

--
HTH,
Bernie
MS Excel MVP
"lisa" <[email protected]> wrote in message
news:[email protected]...
> on excel,i've been using v-lookup to get values in the same row from one
> column but i was wondering if it was possible to use data in 2 columns and
> get a value in the same row to transfer into my data sheet. I looked at
> every function in the program but can't seem to find the way to do that.
if
> anyone knows how to do this, pls respond to this. thanks-lisa

it's still not clear to me how to do it. If i have values in columns A,B and
C in my reference table and need to match up the same values in the same row
in column A and B to return the value of column C in my data then how does
that work? for example:
Reference table
column A|Column B| Column C
blue red purple
red yellow orange
black yellow brown

data table
column A|Column B| Column C
red green
red yellow
blue yellow

SO i want column A and B in the data table to match up so that it gives the
correct C value from the reference table. can anyone further explain how to
use the V-look up for that?
"Bernie Deitrick" wrote:

> Lisa,
>
> VLOOKUP can match a value in the first column and return the value from any
> other column - and the same row as the matched value - just expand the
> second range to include the data that you want to have returned, and use the
> third parameter to specify the column number. For example, to match a value
> in column A and return the value from column C, along the lines of
>
> =VLOOKUP("Lisa",A1:C10,3,FALSE)
>
> The looked-up value can also be a cell reference:
> =VLOOKUP(F1,A1:C10,3,FALSE)
>
> --
> HTH,
> Bernie
> MS Excel MVP
> "lisa" <[email protected]> wrote in message
> news:[email protected]...
> > on excel,i've been using v-lookup to get values in the same row from one
> > column but i was wondering if it was possible to use data in 2 columns and
> > get a value in the same row to transfer into my data sheet. I looked at
> > every function in the program but can't seem to find the way to do that.
> if
> > anyone knows how to do this, pls respond to this. thanks-lisa
>
>
>

I'm sorry. When you said "use data in two columns" I did not think you meant
match data in each of two columns.

That requires something different: and array formula, entered using
Ctrl-Shift-Enter

=INDIRECT("C"&MAX(($A$1:$A$3=A7)*($B$1:$B$3=B7)*(ROW($B$1:$B$3))))

where your reference table is in A1:C3, and your key lookup values are in A7
and B7, and the formula is in C7.

It will return an error if the combination in A7 and B7 doesn't appear in
your reference table.


To use VLOOKUP, you would need to create an new column A, with the formula

=A1&B1

Copied down to match all your reference and lookup data, and then use

=VLOOKUP(A7,$A$1:$D$3,4,FALSE)

HTH,
Bernie
MS Excel MVP

"lisa" <[email protected]> wrote in message
news:[email protected]...
> it's still not clear to me how to do it. If i have values in columns A,B
and
> C in my reference table and need to match up the same values in the same
row
> in column A and B to return the value of column C in my data then how does
> that work? for example:
> Reference table
> column A|Column B| Column C
> blue red purple
> red yellow orange
> black yellow brown
>
> data table
> column A|Column B| Column C
> red green
> red yellow
> blue yellow
>
> SO i want column A and B in the data table to match up so that it gives
the
> correct C value from the reference table. can anyone further explain how
to
> use the V-look up for that?
> "Bernie Deitrick" wrote:
>
> > Lisa,
> >
> > VLOOKUP can match a value in the first column and return the value from
any
> > other column - and the same row as the matched value - just expand the
> > second range to include the data that you want to have returned, and use
the
> > third parameter to specify the column number. For example, to match a
value
> > in column A and return the value from column C, along the lines of
> >
> > =VLOOKUP("Lisa",A1:C10,3,FALSE)
> >
> > The looked-up value can also be a cell reference:
> > =VLOOKUP(F1,A1:C10,3,FALSE)
> >
> > --
> > HTH,
> > Bernie
> > MS Excel MVP
> > "lisa" <[email protected]> wrote in message
> > news:[email protected]...
> > > on excel,i've been using v-lookup to get values in the same row from
one
> > > column but i was wondering if it was possible to use data in 2 columns
and
> > > get a value in the same row to transfer into my data sheet. I looked
at
> > > every function in the program but can't seem to find the way to do
that.
> > if
> > > anyone knows how to do this, pls respond to this. thanks-lisa
> >
> >
> >

Hi bernie,
i'm sorry, i'm still not getting it. I think i need to know how to do this
step by step. Does it make it easier if i said that the reference table is on
a separate worksheet?
I'm not sure if I am suppose to put the formula when i use the vlookup on
the 3rd column or in a new column A that you were mentioning.. so confused...

"Bernie Deitrick" wrote:

> I'm sorry. When you said "use data in two columns" I did not think you meant
> match data in each of two columns.
>
> That requires something different: and array formula, entered using
> Ctrl-Shift-Enter
>
> =INDIRECT("C"&MAX(($A$1:$A$3=A7)*($B$1:$B$3=B7)*(ROW($B$1:$B$3))))
>
> where your reference table is in A1:C3, and your key lookup values are in A7
> and B7, and the formula is in C7.
>
> It will return an error if the combination in A7 and B7 doesn't appear in
> your reference table.
>
>
> To use VLOOKUP, you would need to create an new column A, with the formula
>
> =A1&B1
>
> Copied down to match all your reference and lookup data, and then use
>
> =VLOOKUP(A7,$A$1:$D$3,4,FALSE)
>
> HTH,
> Bernie
> MS Excel MVP
>
> "lisa" <[email protected]> wrote in message
> news:[email protected]...
> > it's still not clear to me how to do it. If i have values in columns A,B
> and
> > C in my reference table and need to match up the same values in the same
> row
> > in column A and B to return the value of column C in my data then how does
> > that work? for example:
> > Reference table
> > column A|Column B| Column C
> > blue red purple
> > red yellow orange
> > black yellow brown
> >
> > data table
> > column A|Column B| Column C
> > red green
> > red yellow
> > blue yellow
> >
> > SO i want column A and B in the data table to match up so that it gives
> the
> > correct C value from the reference table. can anyone further explain how
> to
> > use the V-look up for that?
> > "Bernie Deitrick" wrote:
> >
> > > Lisa,
> > >
> > > VLOOKUP can match a value in the first column and return the value from
> any
> > > other column - and the same row as the matched value - just expand the
> > > second range to include the data that you want to have returned, and use
> the
> > > third parameter to specify the column number. For example, to match a
> value
> > > in column A and return the value from column C, along the lines of
> > >
> > > =VLOOKUP("Lisa",A1:C10,3,FALSE)
> > >
> > > The looked-up value can also be a cell reference:
> > > =VLOOKUP(F1,A1:C10,3,FALSE)
> > >
> > > --
> > > HTH,
> > > Bernie
> > > MS Excel MVP
> > > "lisa" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > on excel,i've been using v-lookup to get values in the same row from
> one
> > > > column but i was wondering if it was possible to use data in 2 columns
> and
> > > > get a value in the same row to transfer into my data sheet. I looked
> at
> > > > every function in the program but can't seem to find the way to do
> that.
> > > if
> > > > anyone knows how to do this, pls respond to this. thanks-lisa
> > >
> > >
> > >
>
>
>

Lisa,

Contact me privately and I will send you a working version.

HTH,
Bernie
MS Excel MVP

"lisa" <[email protected]> wrote in message
news:[email protected]...
> Hi bernie,
> i'm sorry, i'm still not getting it. I think i need to know how to do this
> step by step. Does it make it easier if i said that the reference table is
on
> a separate worksheet?
> I'm not sure if I am suppose to put the formula when i use the vlookup on
> the 3rd column or in a new column A that you were mentioning.. so
confused...
>
> "Bernie Deitrick" wrote:
>
> > I'm sorry. When you said "use data in two columns" I did not think you
meant
> > match data in each of two columns.
> >
> > That requires something different: and array formula, entered using
> > Ctrl-Shift-Enter
> >
> > =INDIRECT("C"&MAX(($A$1:$A$3=A7)*($B$1:$B$3=B7)*(ROW($B$1:$B$3))))
> >
> > where your reference table is in A1:C3, and your key lookup values are
in A7
> > and B7, and the formula is in C7.
> >
> > It will return an error if the combination in A7 and B7 doesn't appear
in
> > your reference table.
> >
> >
> > To use VLOOKUP, you would need to create an new column A, with the
formula
> >
> > =A1&B1
> >
> > Copied down to match all your reference and lookup data, and then use
> >
> > =VLOOKUP(A7,$A$1:$D$3,4,FALSE)
> >
> > HTH,
> > Bernie
> > MS Excel MVP
> >
> > "lisa" <[email protected]> wrote in message
> > news:[email protected]...
> > > it's still not clear to me how to do it. If i have values in columns
A,B
> > and
> > > C in my reference table and need to match up the same values in the
same
> > row
> > > in column A and B to return the value of column C in my data then how
does
> > > that work? for example:
> > > Reference table
> > > column A|Column B| Column C
> > > blue red purple
> > > red yellow orange
> > > black yellow brown
> > >
> > > data table
> > > column A|Column B| Column C
> > > red green
> > > red yellow
> > > blue yellow
> > >
> > > SO i want column A and B in the data table to match up so that it
gives
> > the
> > > correct C value from the reference table. can anyone further explain
how
> > to
> > > use the V-look up for that?
> > > "Bernie Deitrick" wrote:
> > >
> > > > Lisa,
> > > >
> > > > VLOOKUP can match a value in the first column and return the value
from
> > any
> > > > other column - and the same row as the matched value - just expand
the
> > > > second range to include the data that you want to have returned, and
use
> > the
> > > > third parameter to specify the column number. For example, to match
a
> > value
> > > > in column A and return the value from column C, along the lines of
> > > >
> > > > =VLOOKUP("Lisa",A1:C10,3,FALSE)
> > > >
> > > > The looked-up value can also be a cell reference:
> > > > =VLOOKUP(F1,A1:C10,3,FALSE)
> > > >
> > > > --
> > > > HTH,
> > > > Bernie
> > > > MS Excel MVP
> > > > "lisa" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > on excel,i've been using v-lookup to get values in the same row
from
> > one
> > > > > column but i was wondering if it was possible to use data in 2
columns
> > and
> > > > > get a value in the same row to transfer into my data sheet. I
looked
> > at
> > > > > every function in the program but can't seem to find the way to do
> > that.
> > > > if
> > > > > anyone knows how to do this, pls respond to this. thanks-lisa
> > > >
> > > >
> > > >
> >
> >
> >

what's your email address? [email protected]?

"Bernie Deitrick" wrote:

> Lisa,
>
> Contact me privately and I will send you a working version.
>
> HTH,
> Bernie
> MS Excel MVP
>
> "lisa" <[email protected]> wrote in message
> news:[email protected]...
> > Hi bernie,
> > i'm sorry, i'm still not getting it. I think i need to know how to do this
> > step by step. Does it make it easier if i said that the reference table is
> on
> > a separate worksheet?
> > I'm not sure if I am suppose to put the formula when i use the vlookup on
> > the 3rd column or in a new column A that you were mentioning.. so
> confused...
> >
> > "Bernie Deitrick" wrote:
> >
> > > I'm sorry. When you said "use data in two columns" I did not think you
> meant
> > > match data in each of two columns.
> > >
> > > That requires something different: and array formula, entered using
> > > Ctrl-Shift-Enter
> > >
> > > =INDIRECT("C"&MAX(($A$1:$A$3=A7)*($B$1:$B$3=B7)*(ROW($B$1:$B$3))))
> > >
> > > where your reference table is in A1:C3, and your key lookup values are
> in A7
> > > and B7, and the formula is in C7.
> > >
> > > It will return an error if the combination in A7 and B7 doesn't appear
> in
> > > your reference table.
> > >
> > >
> > > To use VLOOKUP, you would need to create an new column A, with the
> formula
> > >
> > > =A1&B1
> > >
> > > Copied down to match all your reference and lookup data, and then use
> > >
> > > =VLOOKUP(A7,$A$1:$D$3,4,FALSE)
> > >
> > > HTH,
> > > Bernie
> > > MS Excel MVP
> > >
> > > "lisa" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > it's still not clear to me how to do it. If i have values in columns
> A,B
> > > and
> > > > C in my reference table and need to match up the same values in the
> same
> > > row
> > > > in column A and B to return the value of column C in my data then how
> does
> > > > that work? for example:
> > > > Reference table
> > > > column A|Column B| Column C
> > > > blue red purple
> > > > red yellow orange
> > > > black yellow brown
> > > >
> > > > data table
> > > > column A|Column B| Column C
> > > > red green
> > > > red yellow
> > > > blue yellow
> > > >
> > > > SO i want column A and B in the data table to match up so that it
> gives
> > > the
> > > > correct C value from the reference table. can anyone further explain
> how
> > > to
> > > > use the V-look up for that?
> > > > "Bernie Deitrick" wrote:
> > > >
> > > > > Lisa,
> > > > >
> > > > > VLOOKUP can match a value in the first column and return the value
> from
> > > any
> > > > > other column - and the same row as the matched value - just expand
> the
> > > > > second range to include the data that you want to have returned, and
> use
> > > the
> > > > > third parameter to specify the column number. For example, to match
> a
> > > value
> > > > > in column A and return the value from column C, along the lines of
> > > > >
> > > > > =VLOOKUP("Lisa",A1:C10,3,FALSE)
> > > > >
> > > > > The looked-up value can also be a cell reference:
> > > > > =VLOOKUP(F1,A1:C10,3,FALSE)
> > > > >
> > > > > --
> > > > > HTH,
> > > > > Bernie
> > > > > MS Excel MVP
> > > > > "lisa" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > on excel,i've been using v-lookup to get values in the same row
> from
> > > one
> > > > > > column but i was wondering if it was possible to use data in 2
> columns
> > > and
> > > > > > get a value in the same row to transfer into my data sheet. I
> looked
> > > at
> > > > > > every function in the program but can't seem to find the way to do
> > > that.
> > > > > if
> > > > > > anyone knows how to do this, pls respond to this. thanks-lisa
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

Lisa,

Remove the "dot" and you've got it.

HTH,
Bernie
MS Excel MVP

"lisa" <[email protected]> wrote in message
news:[email protected]...
> what's your email address? [email protected]?
>
> "Bernie Deitrick" wrote:
>
> > Lisa,
> >
> > Contact me privately and I will send you a working version.
> >
> > HTH,
> > Bernie
> > MS Excel MVP
> >
> > "lisa" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hi bernie,
> > > i'm sorry, i'm still not getting it. I think i need to know how to do
this
> > > step by step. Does it make it easier if i said that the reference
table is
> > on
> > > a separate worksheet?
> > > I'm not sure if I am suppose to put the formula when i use the vlookup
on
> > > the 3rd column or in a new column A that you were mentioning.. so
> > confused...
> > >
> > > "Bernie Deitrick" wrote:
> > >
> > > > I'm sorry. When you said "use data in two columns" I did not think
you
> > meant
> > > > match data in each of two columns.
> > > >
> > > > That requires something different: and array formula, entered using
> > > > Ctrl-Shift-Enter
> > > >
> > > > =INDIRECT("C"&MAX(($A$1:$A$3=A7)*($B$1:$B$3=B7)*(ROW($B$1:$B$3))))
> > > >
> > > > where your reference table is in A1:C3, and your key lookup values
are
> > in A7
> > > > and B7, and the formula is in C7.
> > > >
> > > > It will return an error if the combination in A7 and B7 doesn't
appear
> > in
> > > > your reference table.
> > > >
> > > >
> > > > To use VLOOKUP, you would need to create an new column A, with the
> > formula
> > > >
> > > > =A1&B1
> > > >
> > > > Copied down to match all your reference and lookup data, and then
use
> > > >
> > > > =VLOOKUP(A7,$A$1:$D$3,4,FALSE)
> > > >
> > > > HTH,
> > > > Bernie
> > > > MS Excel MVP
> > > >
> > > > "lisa" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > it's still not clear to me how to do it. If i have values in
columns
> > A,B
> > > > and
> > > > > C in my reference table and need to match up the same values in
the
> > same
> > > > row
> > > > > in column A and B to return the value of column C in my data then
how
> > does
> > > > > that work? for example:
> > > > > Reference table
> > > > > column A|Column B| Column C
> > > > > blue red purple
> > > > > red yellow orange
> > > > > black yellow brown
> > > > >
> > > > > data table
> > > > > column A|Column B| Column C
> > > > > red green
> > > > > red yellow
> > > > > blue yellow
> > > > >
> > > > > SO i want column A and B in the data table to match up so that it
> > gives
> > > > the
> > > > > correct C value from the reference table. can anyone further
explain
> > how
> > > > to
> > > > > use the V-look up for that?
> > > > > "Bernie Deitrick" wrote:
> > > > >
> > > > > > Lisa,
> > > > > >
> > > > > > VLOOKUP can match a value in the first column and return the
value
> > from
> > > > any
> > > > > > other column - and the same row as the matched value - just
expand
> > the
> > > > > > second range to include the data that you want to have returned,
and
> > use
> > > > the
> > > > > > third parameter to specify the column number. For example, to
match
> > a
> > > > value
> > > > > > in column A and return the value from column C, along the lines
of
> > > > > >
> > > > > > =VLOOKUP("Lisa",A1:C10,3,FALSE)
> > > > > >
> > > > > > The looked-up value can also be a cell reference:
> > > > > > =VLOOKUP(F1,A1:C10,3,FALSE)
> > > > > >
> > > > > > --
> > > > > > HTH,
> > > > > > Bernie
> > > > > > MS Excel MVP
> > > > > > "lisa" <[email protected]> wrote in message
> > > > > > news:[email protected]...
> > > > > > > on excel,i've been using v-lookup to get values in the same
row
> > from
> > > > one
> > > > > > > column but i was wondering if it was possible to use data in 2
> > columns
> > > > and
> > > > > > > get a value in the same row to transfer into my data sheet. I
> > looked
> > > > at
> > > > > > > every function in the program but can't seem to find the way
to do
> > > > that.
> > > > > > if
> > > > > > > anyone knows how to do this, pls respond to this. thanks-lisa
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >