Hello All,
I am trying to pull in a value into a spreadhseet using Vlookup. I have
figured out how to do this with one lookup value(cell), I am having trouble
trying to get this to work when there are 3 different cells I need for the
lookup value. What I am ultimately trying to do is pull a value into my
spreadsheet from a refrence sheet when the three cells match. I don't know
if vlookup is something I should be using. The criteria will always match my
refrence sheet, I just need the variable value from my refrence sheet to be
dropped in when it finds a match. I hope this makes some sense. If anyone
has any ideas how I could do this I would appreciate your help.


i.e.
Cell Criteria 1 Criteria 2 Criteria 3 =value from DB
spreadsheet
a1 ad 1 mp 1 sz 1 Variable #
a2 ad 2 mp 2 sz 2 Variable #
a3 ad 3 mp 3 sz 3 Variable #
a4 ad 4 mp 4 sz 4 Variable #

Hi,

Maybe...

=SUMPRODUCT((criteria1)*(criteria2)*(criteria3),ra nge to lookin)

Hope this helps!
Jean-Guy

"staticx5151" wrote:

Hello All,
I am trying to pull in a value into a spreadhseet using Vlookup. I have
figured out how to do this with one lookup value(cell), I am having trouble
trying to get this to work when there are 3 different cells I need for the
lookup value. What I am ultimately trying to do is pull a value into my
spreadsheet from a refrence sheet when the three cells match. I don't know
if vlookup is something I should be using. The criteria will always match my
refrence sheet, I just need the variable value from my refrence sheet to be
dropped in when it finds a match. I hope this makes some sense. If anyone
has any ideas how I could do this I would appreciate your help.


i.e.
Cell Criteria 1 Criteria 2 Criteria 3 =value from DB
spreadsheet
a1 ad 1 mp 1 sz 1 Variable #
a2 ad 2 mp 2 sz 2 Variable #
a3 ad 3 mp 3 sz 3 Variable #
a4 ad 4 mp 4 sz 4 Variable #

Thanks for the reply, unfortunately I think the problem I am having is a
little different. The refrence sheet I mentioned is populated by three
fields:
Property, section, and unit, which a report dynamically assigns a value that
changes from day to day.

In my master spreadsheet I also have Property, section, and unit which
contains other data values. So what I am trying to do is create a formula
that will pull in the variable value from my refrence sheet, and display it
in the same line item with the other data based on three values Property,
section, and unit.

Because Property can be the same, and section can be the same in different
line items, I need to have the three fields be used as lookup criteria to
ensure that the correct value is pulled in and associated to the correct
placment (defined by Property/section/unit) , a regular VLookup will not work
because it only allows you to use one lookup value. Which is why I was
hoping there would be something I could do to have it lookup and match 3
values. Complicated I know, thanks for your help.


"pinmaster" wrote:

Hi,

Maybe...

=SUMPRODUCT((criteria1)*(criteria2)*(criteria3),ra nge to lookin)

Hope this helps!
Jean-Guy

"staticx5151" wrote:

Hello All,
I am trying to pull in a value into a spreadhseet using Vlookup. I have
figured out how to do this with one lookup value(cell), I am having trouble
trying to get this to work when there are 3 different cells I need for the
lookup value. What I am ultimately trying to do is pull a value into my
spreadsheet from a refrence sheet when the three cells match. I don't know
if vlookup is something I should be using. The criteria will always match my
refrence sheet, I just need the variable value from my refrence sheet to be
dropped in when it finds a match. I hope this makes some sense. If anyone
has any ideas how I could do this I would appreciate your help.


i.e.
Cell Criteria 1 Criteria 2 Criteria 3 =value from DB
spreadsheet
a1 ad 1 mp 1 sz 1 Variable #
a2 ad 2 mp 2 sz 2 Variable #
a3 ad 3 mp 3 sz 3 Variable #
a4 ad 4 mp 4 sz 4 Variable #

If you're returning a number, you may want to try that =sumproduct() formula.
It just may work better than you think...

Saved from a previous post:

If you want exact matches for just two columns (and return a value from a
third), you could use:

=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet !$b$1:$b$100),0))

(all in one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.

And you can add more conditions by just adding more stuff to that product
portion of the formula:

=index(othersheet!$d$1:$d$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100)
*(c2=othersheet!$c$1:$c$100),0))


staticx5151 wrote:

Thanks for the reply, unfortunately I think the problem I am having is a
little different. The refrence sheet I mentioned is populated by three
fields:
Property, section, and unit, which a report dynamically assigns a value that
changes from day to day.

In my master spreadsheet I also have Property, section, and unit which
contains other data values. So what I am trying to do is create a formula
that will pull in the variable value from my refrence sheet, and display it
in the same line item with the other data based on three values Property,
section, and unit.

Because Property can be the same, and section can be the same in different
line items, I need to have the three fields be used as lookup criteria to
ensure that the correct value is pulled in and associated to the correct
placment (defined by Property/section/unit) , a regular VLookup will not work
because it only allows you to use one lookup value. Which is why I was
hoping there would be something I could do to have it lookup and match 3
values. Complicated I know, thanks for your help.

"pinmaster" wrote:

Hi,

Maybe...

=SUMPRODUCT((criteria1)*(criteria2)*(criteria3),ra nge to lookin)

Hope this helps!
Jean-Guy

"staticx5151" wrote:

Hello All,
I am trying to pull in a value into a spreadhseet using Vlookup. I have
figured out how to do this with one lookup value(cell), I am having trouble
trying to get this to work when there are 3 different cells I need for the
lookup value. What I am ultimately trying to do is pull a value into my
spreadsheet from a refrence sheet when the three cells match. I don't know
if vlookup is something I should be using. The criteria will always match my
refrence sheet, I just need the variable value from my refrence sheet to be
dropped in when it finds a match. I hope this makes some sense. If anyone
has any ideas how I could do this I would appreciate your help.


i.e.
Cell Criteria 1 Criteria 2 Criteria 3 =value from DB
spreadsheet
a1 ad 1 mp 1 sz 1 Variable #
a2 ad 2 mp 2 sz 2 Variable #
a3 ad 3 mp 3 sz 3 Variable #
a4 ad 4 mp 4 sz 4 Variable #



--

Dave Peterson