|
|
Lisa,
Remove the "dot" and you've got it.
HTH,
Bernie
MS Excel MVP
"lisa" wrote in message
...
what's your email address? ?
"Bernie Deitrick" wrote:
Lisa,
Contact me privately and I will send you a working version.
HTH,
Bernie
MS Excel MVP
"lisa" wrote in message
...
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)*(R OW($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" wrote in message
...
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" wrote in message
...
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
|