| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Vlookup for 2 items
Hi
I know the Vlookup formula =VLOOKUP(A2,B5:C10,2,FALSE) Can we change the lookup value ( A2 ) To two cells Thanks |
|
#2
|
|||
|
|||
|
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" wrote in message ... Hi I know the Vlookup formula =VLOOKUP(A2,B5:C10,2,FALSE) Can we change the lookup value ( A2 ) To two cells Thanks |
|
#3
|
|||
|
|||
|
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" wrote in message ... Hi I know the Vlookup formula =VLOOKUP(A2,B5:C10,2,FALSE) Can we change the lookup value ( A2 ) To two cells Thanks |
|
#4
|
|||
|
|||
|
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 |
|
#5
|
|||
|
|||
|
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 |
|
#6
|
|||
|
|||
|
May I ask what is the meaning of ok1 and ok2? THanK -- mrwhiteboy ------------------------------------------------------------------------ mrwhiteboy's Profile: http://www.excelforum.com/member.php...o&userid=25255 View this thread: http://www.excelforum.com/showthread...hreadid=387113 |
|
#7
|
|||
|
|||
|
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 |
|
#8
|
|||
|
|||
|
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 |
|
#9
|
|||
|
|||
|
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" 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" wrote in message ... Hi I know the Vlookup formula =VLOOKUP(A2,B5:C10,2,FALSE) Can we change the lookup value ( A2 ) To two cells Thanks |
|
#10
|
|||
|
|||
|
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" wrote in message ... 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" 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" wrote in message ... Hi I know the Vlookup formula =VLOOKUP(A2,B5:C10,2,FALSE) Can we change the lookup value ( A2 ) To two cells Thanks |
|
#11
|
|||
|
|||
|
Thanks Anne
I didnt even think it would be that easy. You are a star Jeanette "Anne Troy" 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" wrote in message ... 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" 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" wrote in message ... Hi I know the Vlookup formula =VLOOKUP(A2,B5:C10,2,FALSE) Can we change the lookup value ( A2 ) To two cells Thanks |
|
#12
|
|||
|
|||
|
Thanks, Jeanette. :)
******************* ~Anne Troy www.OfficeArticles.com "jeanette.rimmer" wrote in message ... Thanks Anne I didnt even think it would be that easy. You are a star Jeanette "Anne Troy" 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" wrote in message ... 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" 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" wrote in message ... Hi I know the Vlookup formula =VLOOKUP(A2,B5:C10,2,FALSE) Can we change the lookup value ( A2 ) To two cells Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VLOOKUP not returning results | Excel Worksheet Functions | |||
| Banging head against wall with VLOOKUP | Excel Discussion (Misc queries) | |||
| Which to use - if, vlookup, match, index, offset, vba? | Excel Discussion (Misc queries) | |||
| Array Function with VLOOKUP | Excel Worksheet Functions | |||
| vlookup data hidden within worksheet | Excel Worksheet Functions | |||
Linear Mode
