Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
3 column lookup for 4th column value
I have four columns of data. The first three are the lookup indicies for the
4th column I want to use as my result. Can Lookup, Index or Match do this? Ken |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
3 column lookup for 4th column value
Hi Ken,
One "back door" approach.... Insert a blank column D. In D1 enter the formula =A1 & B1 & C1 and copy down all rows. Use this column as your looked up value (index). Do the same thing for the values you want to use for the look up and use VLOOKUP. -- Ken Hudson "Ken King" wrote: I have four columns of data. The first three are the lookup indicies for the 4th column I want to use as my result. Can Lookup, Index or Match do this? Ken |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
3 column lookup for 4th column value
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)) Ken King wrote: I have four columns of data. The first three are the lookup indicies for the 4th column I want to use as my result. Can Lookup, Index or Match do this? Ken -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
3 column lookup for 4th column value
If I understand you, say your data is in A1 to D100, and you want to return
the value in Column D, that is on the same row where your criteria is met in the first 3 columns. With the criteria for Column A entered in E1, Column B in E2, And Column C in E3, Try this *array* formula: =INDEX(D1:D100,MATCH(1,(A1:A100=E1)*(B1:B100=E2)*( C1:C100=E3),0)) -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, you must use CSE when revising the formula. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Ken King" wrote in message ... I have four columns of data. The first three are the lookup indicies for the 4th column I want to use as my result. Can Lookup, Index or Match do this? Ken |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
3 column lookup for 4th column value
Ken,
I like this since it is simple, but it didn't work initially. Until I found that the lookup vector in the array had to be in Acending order. "Ken Hudson" wrote: Hi Ken, One "back door" approach.... Insert a blank column D. In D1 enter the formula =A1 & B1 & C1 and copy down all rows. Use this column as your looked up value (index). Do the same thing for the values you want to use for the look up and use VLOOKUP. -- Ken Hudson "Ken King" wrote: I have four columns of data. The first three are the lookup indicies for the 4th column I want to use as my result. Can Lookup, Index or Match do this? Ken |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup by row and column | Excel Discussion (Misc queries) | |||
Based on a condition in one column, search for a year in another column, and display data from another column in the same row look | Excel Discussion (Misc queries) | |||
Lookup in one column, and return value from another column | Excel Worksheet Functions | |||
Column Lookup | Excel Discussion (Misc queries) | |||
Calculating totals in a column based on a lookup in another column | Excel Worksheet Functions |