Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF for dynamic custom lookup?
I have a data range called Benefits. This range does not include the header
row - that is a seperate range named BenefitHeader. The data looks something like this: Name Owner Terminated Var1 LTC 0 12/31/2007 TRUE LTC 0 TRUE SRP 1 12/31/2007 EP SRP 0 EP SRP 1 S162 SRP 0 S162 I need to be able to lookup based on Name and any one or two of the other columns within the named range (Currently, the columns used are A:IE and more could be added at any time). To do a single lookup value I would just use =VLOOKUP("LTC",Benefits,MATCH("terminated",Benefit Header,0)) and that would get the first instance of LTC and pull the terminated value. However, how would I get, for example, the terminated value for SRP where owner is 1 and Var1 is S162? I'd like to do this without concatenating columns, if possible, because I want to keep it flexible when determining which columns to use. I am open to adding a UDF where I pass it the column headings for values I want to specify and the column heading for the value that I would like to get. I think it must be possible but I don't know how to do it. Any help will be appreciated!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF for dynamic custom lookup?
You don't need a UDF for this.
==== 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 only use the whole column in xl2007. 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)) Robin wrote: I have a data range called Benefits. This range does not include the header row - that is a seperate range named BenefitHeader. The data looks something like this: Name Owner Terminated Var1 LTC 0 12/31/2007 TRUE LTC 0 TRUE SRP 1 12/31/2007 EP SRP 0 EP SRP 1 S162 SRP 0 S162 I need to be able to lookup based on Name and any one or two of the other columns within the named range (Currently, the columns used are A:IE and more could be added at any time). To do a single lookup value I would just use =VLOOKUP("LTC",Benefits,MATCH("terminated",Benefit Header,0)) and that would get the first instance of LTC and pull the terminated value. However, how would I get, for example, the terminated value for SRP where owner is 1 and Var1 is S162? I'd like to do this without concatenating columns, if possible, because I want to keep it flexible when determining which columns to use. I am open to adding a UDF where I pass it the column headings for values I want to specify and the column heading for the value that I would like to get. I think it must be possible but I don't know how to do it. Any help will be appreciated!! -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF for dynamic custom lookup?
I see you have an active thread elsewhere.
Dave Peterson wrote: You don't need a UDF for this. ==== 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 only use the whole column in xl2007. 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)) Robin wrote: I have a data range called Benefits. This range does not include the header row - that is a seperate range named BenefitHeader. The data looks something like this: Name Owner Terminated Var1 LTC 0 12/31/2007 TRUE LTC 0 TRUE SRP 1 12/31/2007 EP SRP 0 EP SRP 1 S162 SRP 0 S162 I need to be able to lookup based on Name and any one or two of the other columns within the named range (Currently, the columns used are A:IE and more could be added at any time). To do a single lookup value I would just use =VLOOKUP("LTC",Benefits,MATCH("terminated",Benefit Header,0)) and that would get the first instance of LTC and pull the terminated value. However, how would I get, for example, the terminated value for SRP where owner is 1 and Var1 is S162? I'd like to do this without concatenating columns, if possible, because I want to keep it flexible when determining which columns to use. I am open to adding a UDF where I pass it the column headings for values I want to specify and the column heading for the value that I would like to get. I think it must be possible but I don't know how to do it. Any help will be appreciated!! -- Dave Peterson -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF for dynamic custom lookup?
Yes, I thought maybe different people look at the different forums. I will
try your solution and see if it works. Thanks!! "Dave Peterson" wrote: I see you have an active thread elsewhere. Dave Peterson wrote: You don't need a UDF for this. ==== 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 only use the whole column in xl2007. 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)) Robin wrote: I have a data range called Benefits. This range does not include the header row - that is a seperate range named BenefitHeader. The data looks something like this: Name Owner Terminated Var1 LTC 0 12/31/2007 TRUE LTC 0 TRUE SRP 1 12/31/2007 EP SRP 0 EP SRP 1 S162 SRP 0 S162 I need to be able to lookup based on Name and any one or two of the other columns within the named range (Currently, the columns used are A:IE and more could be added at any time). To do a single lookup value I would just use =VLOOKUP("LTC",Benefits,MATCH("terminated",Benefit Header,0)) and that would get the first instance of LTC and pull the terminated value. However, how would I get, for example, the terminated value for SRP where owner is 1 and Var1 is S162? I'd like to do this without concatenating columns, if possible, because I want to keep it flexible when determining which columns to use. I am open to adding a UDF where I pass it the column headings for values I want to specify and the column heading for the value that I would like to get. I think it must be possible but I don't know how to do it. Any help will be appreciated!! -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF for dynamic custom lookup?
It's the same solution as you got in the other newsgroup.
Robin wrote: Yes, I thought maybe different people look at the different forums. I will try your solution and see if it works. Thanks!! "Dave Peterson" wrote: I see you have an active thread elsewhere. Dave Peterson wrote: You don't need a UDF for this. ==== 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 only use the whole column in xl2007. 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)) Robin wrote: I have a data range called Benefits. This range does not include the header row - that is a seperate range named BenefitHeader. The data looks something like this: Name Owner Terminated Var1 LTC 0 12/31/2007 TRUE LTC 0 TRUE SRP 1 12/31/2007 EP SRP 0 EP SRP 1 S162 SRP 0 S162 I need to be able to lookup based on Name and any one or two of the other columns within the named range (Currently, the columns used are A:IE and more could be added at any time). To do a single lookup value I would just use =VLOOKUP("LTC",Benefits,MATCH("terminated",Benefit Header,0)) and that would get the first instance of LTC and pull the terminated value. However, how would I get, for example, the terminated value for SRP where owner is 1 and Var1 is S162? I'd like to do this without concatenating columns, if possible, because I want to keep it flexible when determining which columns to use. I am open to adding a UDF where I pass it the column headings for values I want to specify and the column heading for the value that I would like to get. I think it must be possible but I don't know how to do it. Any help will be appreciated!! -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF for dynamic custom lookup?
Robin,
See my reply in worksheetfunctions, though it is a VBA solution (a UDF). HTH, Bernie MS Excel MVP "Robin" wrote in message ... I have a data range called Benefits. This range does not include the header row - that is a seperate range named BenefitHeader. The data looks something like this: Name Owner Terminated Var1 LTC 0 12/31/2007 TRUE LTC 0 TRUE SRP 1 12/31/2007 EP SRP 0 EP SRP 1 S162 SRP 0 S162 I need to be able to lookup based on Name and any one or two of the other columns within the named range (Currently, the columns used are A:IE and more could be added at any time). To do a single lookup value I would just use =VLOOKUP("LTC",Benefits,MATCH("terminated",Benefit Header,0)) and that would get the first instance of LTC and pull the terminated value. However, how would I get, for example, the terminated value for SRP where owner is 1 and Var1 is S162? I'd like to do this without concatenating columns, if possible, because I want to keep it flexible when determining which columns to use. I am open to adding a UDF where I pass it the column headings for values I want to specify and the column heading for the value that I would like to get. I think it must be possible but I don't know how to do it. Any help will be appreciated!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup Dynamic Value in a Table | Excel Worksheet Functions | |||
Dynamic Custom Number Format | Excel Discussion (Misc queries) | |||
Dynamic Worksheet Lookup | Excel Worksheet Functions | |||
dynamic lookup | Excel Worksheet Functions | |||
using LOOKUP instead of IF on dynamic row | Excel Worksheet Functions |