# Microsoft Office Application Help - Excel Help forum > Excel Formulas & Functions >  > [SOLVED] Vlookup for 2 items

## Mostafa

Hi
I know the Vlookup formula
=VLOOKUP(A2,B5:C10,2,FALSE)
Can we change the lookup value ( A2 )
To two cells
Thanks

----------


## Anne Troy

I have done this a few times. In my lookup table, I create a 3rd column,
where I put =A1&B1. This strings 2 values together.
Then you can do a vlookup for A2&B2.
*******************
~Anne Troy

www.OfficeArticles.com


"Mostafa" <Mostafa@discussions.microsoft.com> wrote in message
news:97FB28E2-852D-4724-BA51-0547DCD9A1CB@microsoft.com...
> Hi
> I know the Vlookup formula
> =VLOOKUP(A2,B5:C10,2,FALSE)
> Can we change the lookup value ( A2 )
> To two cells
> Thanks
>

----------


## CLR

This will look up the sum of A2 and B2 in your existing table
=VLOOKUP(A2+B2,B5:C10,2,FALSE)

A new column must be added to the left side of your table with the
CONCATENATED values you wish to look up, then a CONCATENATED lookup would
be.........
=VLOOKUP(A2&B2,A5:C10,2,FALSE)

Vaya con Dios,
Chuck, CABGx3




"Mostafa" <Mostafa@discussions.microsoft.com> wrote in message
news:97FB28E2-852D-4724-BA51-0547DCD9A1CB@microsoft.com...
> Hi
> I know the Vlookup formula
> =VLOOKUP(A2,B5:C10,2,FALSE)
> Can we change the lookup value ( A2 )
> To two cells
> Thanks
>

----------


## Dave Peterson

I like this syntax:

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



Mostafa wrote:
>
> Hi
> I know the Vlookup formula
> =VLOOKUP(A2,B5:C10,2,FALSE)
> Can we change the lookup value ( A2 )
> To two cells
> Thanks

--

Dave Peterson

----------


## Alan Beban

If you use values rather than cell references, something like the
following, array entered into a 2-cell row, will return the sought results:

=VLOOKUP({"ok1","ok2"},B5:C10,2,FALSE)

or =VLOOKUP({"ok1";"ok2"},B5:C10,2,FALSE) array entered into a 2-cell
column.

Alan Beban

Dave Peterson wrote:
> I like this syntax:
>
> =index(othersheet!$c$1:$c$10,
>    match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet!$b$1:$b$100),0))
>
>
>
> Mostafa wrote:
>
>>Hi
>>I know the Vlookup formula
>>=VLOOKUP(A2,B5:C10,2,FALSE)
>>Can we change the lookup value ( A2 )
>>To two cells
>>Thanks
>
>

----------


## mrwhiteboy

May I ask what is the meaning of ok1 and ok2?

THanK

----------


## Alan Beban

And you can, in fact, use cell references.  Array enter into a 2-cell
column, e.g.:

=VLOOKUP((A2:A3),B5:C10,2,FALSE)

Alan Beban

Alan Beban wrote:
> If you use values rather than cell references, something like the
> following, array entered into a 2-cell row, will return the sought results:
>
> =VLOOKUP({"ok1","ok2"},B5:C10,2,FALSE)
>
> or =VLOOKUP({"ok1";"ok2"},B5:C10,2,FALSE) array entered into a 2-cell
> column.
>
> Alan Beban
>
> Dave Peterson wrote:
>
>> I like this syntax:
>>
>> =index(othersheet!$c$1:$c$10,
>>    match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet!$b$1:$b$100),0))
>>
>>
>>
>> Mostafa wrote:
>>
>>> Hi
>>> I know the Vlookup formula
>>> =VLOOKUP(A2,B5:C10,2,FALSE)
>>> Can we change the lookup value ( A2 )
>>> To two cells
>>> Thanks
>>
>>
>>

----------


## Alan Beban

mrwhiteboy wrote:
> May I ask what is the meaning of ok1 and ok2?
>
> THanK
>
>
They were just arbitrary values in the range B5:B10 to illustrate that
the formula works if one uses values rather than cell references; but
see my post of 4:49 this morning.

Alan Beban

----------


## jeanette.rimmer

Hi, Anne

Ive set this up so that I can pick up the pay rate from a lookup table for a
combination of contract and paying agency. I now need to multiply the value
that this look up has given by the hrs that the person worked.

Can I do this with Vlookups? and a sum?


Confused


Jeanette

"Anne Troy" <ng@officearticles.com> wrote in message
news:195ea$42d641ee$97c5108d$7123@allthenewsgroups.com...
>I have done this a few times. In my lookup table, I create a 3rd column,
> where I put =A1&B1. This strings 2 values together.
> Then you can do a vlookup for A2&B2.
> *******************
> ~Anne Troy
>
> www.OfficeArticles.com
>
>
> "Mostafa" <Mostafa@discussions.microsoft.com> wrote in message
> news:97FB28E2-852D-4724-BA51-0547DCD9A1CB@microsoft.com...
>> Hi
>> I know the Vlookup formula
>> =VLOOKUP(A2,B5:C10,2,FALSE)
>> Can we change the lookup value ( A2 )
>> To two cells
>> Thanks
>>
>
>

----------


## Anne Troy

You should be able to, yes. Something like...

=vlookup(....)*a1
Where a1 contains the number of hours worked.
*******************
~Anne Troy

www.OfficeArticles.com


"jeanette.rimmer" <jeanette.rimmer@ntlworld.com> wrote in message
news:5gRCe.7066$vv6.5577@newsfe6-gui.ntli.net...
> Hi, Anne
>
> Ive set this up so that I can pick up the pay rate from a lookup table for
a
> combination of contract and paying agency. I now need to multiply the
value
> that this look up has given by the hrs that the person worked.
>
> Can I do this with Vlookups? and a sum?
>
>
> Confused
>
>
> Jeanette
>
> "Anne Troy" <ng@officearticles.com> wrote in message
> news:195ea$42d641ee$97c5108d$7123@allthenewsgroups.com...
> >I have done this a few times. In my lookup table, I create a 3rd column,
> > where I put =A1&B1. This strings 2 values together.
> > Then you can do a vlookup for A2&B2.
> > *******************
> > ~Anne Troy
> >
> > www.OfficeArticles.com
> >
> >
> > "Mostafa" <Mostafa@discussions.microsoft.com> wrote in message
> > news:97FB28E2-852D-4724-BA51-0547DCD9A1CB@microsoft.com...
> >> Hi
> >> I know the Vlookup formula
> >> =VLOOKUP(A2,B5:C10,2,FALSE)
> >> Can we change the lookup value ( A2 )
> >> To two cells
> >> Thanks
> >>
> >
> >
>
>

----------


## jeanette.rimmer

Thanks Anne

I didnt even think it would be that easy.  You are a star


Jeanette


"Anne Troy" <ng@officearticles.com> wrote in message
news:df60$42dc09e7$97c5108d$11924@allthenewsgroups.com...
> You should be able to, yes. Something like...
>
> =vlookup(....)*a1
> Where a1 contains the number of hours worked.
> *******************
> ~Anne Troy
>
> www.OfficeArticles.com
>
>
> "jeanette.rimmer" <jeanette.rimmer@ntlworld.com> wrote in message
> news:5gRCe.7066$vv6.5577@newsfe6-gui.ntli.net...
>> Hi, Anne
>>
>> Ive set this up so that I can pick up the pay rate from a lookup table
>> for
> a
>> combination of contract and paying agency. I now need to multiply the
> value
>> that this look up has given by the hrs that the person worked.
>>
>> Can I do this with Vlookups? and a sum?
>>
>>
>> Confused
>>
>>
>> Jeanette
>>
>> "Anne Troy" <ng@officearticles.com> wrote in message
>> news:195ea$42d641ee$97c5108d$7123@allthenewsgroups.com...
>> >I have done this a few times. In my lookup table, I create a 3rd column,
>> > where I put =A1&B1. This strings 2 values together.
>> > Then you can do a vlookup for A2&B2.
>> > *******************
>> > ~Anne Troy
>> >
>> > www.OfficeArticles.com
>> >
>> >
>> > "Mostafa" <Mostafa@discussions.microsoft.com> wrote in message
>> > news:97FB28E2-852D-4724-BA51-0547DCD9A1CB@microsoft.com...
>> >> Hi
>> >> I know the Vlookup formula
>> >> =VLOOKUP(A2,B5:C10,2,FALSE)
>> >> Can we change the lookup value ( A2 )
>> >> To two cells
>> >> Thanks
>> >>
>> >
>> >
>>
>>
>
>

----------


## Anne Troy

Thanks, Jeanette.  :Smilie: 
*******************
~Anne Troy

www.OfficeArticles.com


"jeanette.rimmer" <jeanette.rimmer@ntlworld.com> wrote in message
news:mTTCe.4112$jo3.3310@newsfe5-win.ntli.net...
> Thanks Anne
>
> I didnt even think it would be that easy.  You are a star
>
>
> Jeanette
>
>
> "Anne Troy" <ng@officearticles.com> wrote in message
> news:df60$42dc09e7$97c5108d$11924@allthenewsgroups.com...
> > You should be able to, yes. Something like...
> >
> > =vlookup(....)*a1
> > Where a1 contains the number of hours worked.
> > *******************
> > ~Anne Troy
> >
> > www.OfficeArticles.com
> >
> >
> > "jeanette.rimmer" <jeanette.rimmer@ntlworld.com> wrote in message
> > news:5gRCe.7066$vv6.5577@newsfe6-gui.ntli.net...
> >> Hi, Anne
> >>
> >> Ive set this up so that I can pick up the pay rate from a lookup table
> >> for
> > a
> >> combination of contract and paying agency. I now need to multiply the
> > value
> >> that this look up has given by the hrs that the person worked.
> >>
> >> Can I do this with Vlookups? and a sum?
> >>
> >>
> >> Confused
> >>
> >>
> >> Jeanette
> >>
> >> "Anne Troy" <ng@officearticles.com> wrote in message
> >> news:195ea$42d641ee$97c5108d$7123@allthenewsgroups.com...
> >> >I have done this a few times. In my lookup table, I create a 3rd
column,
> >> > where I put =A1&B1. This strings 2 values together.
> >> > Then you can do a vlookup for A2&B2.
> >> > *******************
> >> > ~Anne Troy
> >> >
> >> > www.OfficeArticles.com
> >> >
> >> >
> >> > "Mostafa" <Mostafa@discussions.microsoft.com> wrote in message
> >> > news:97FB28E2-852D-4724-BA51-0547DCD9A1CB@microsoft.com...
> >> >> Hi
> >> >> I know the Vlookup formula
> >> >> =VLOOKUP(A2,B5:C10,2,FALSE)
> >> >> Can we change the lookup value ( A2 )
> >> >> To two cells
> >> >> Thanks
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
>

----------


## hpn88

Hi

I'm trying to look up for two values as well but they are not exact values. Specifically, I'm looking to pick a "supply unit" based on the amperage i need at the two voltages that are supplied. So the data in the look up table will be

115V	/230V       /Model#
0A	/10A         /1
1.1A	/2.1A        /2
1.4A	/4.1A        /3
1.7A	/5A           /4
1.7A	/6.1A        /5
5A	/2A           /6
5A	/5A           /7
5A	/7A           /8
10A	/0A           /9


The lookup value could be something like 4.7A at 115V and 6.1 at 230V. I would need the formula to pick model #8.

I tried something like this so far.
=INDEX(Transformer!N4:P13,MATCH(Z52,OFFSET(Transformer!O4,MATCH(Z51,Transformer!N4:N13,1),0,14),1)+MATCH(Z51,Transformer!N4:N13,1)+1,3)

Thanks to anyone who attempts to tickle this problem.

----------

