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

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 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

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

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