Lookup Help

[RLJ]

=INDEX(J3:J54,MATCH(L2,C3:I54,0))

I'm having a problem with the C3:I54 range on this formula. I'm guessing that MATCH may not be what I want. I need to return the value in J3:J54 from looking up whats in L2 in the C3:I54 range.

Thanks for your help. I always appreciate it.

 

[philwojo]

Try and flip those arrays around, try this:

[COLOR=#333333]=INDEX(C3:J54,MATCH(L2,[/COLOR][COLOR=#ff0000]C3:C54[/COLOR][COLOR=#333333],0),10)[/COLOR]

But if you are saying your match value can be anywhere in columns C:I then I am not sure off hand.

 

[RLJ]

It did not work. Yes the match value can be anywhere in Columns C:I.

 

[cerfani]

i dont think you can use match when both dimensions of the lookup range is greater than 1 cell...

match returns the position in the range it found the lookup value... it only returns a single number and to lookup a value in a 2 dimensional table you need 2 numbers to describe its position so you need to adjust your lookup range to be y * 1 in size

 

[markmzz]

=INDEX(J3:J54,MATCH(L2,C3:I54,0))

I'm having a problem with the C3:I54 range on this formula. I'm guessing that MATCH may not be what I want. I need to return the value in J3:J54 from looking up whats in L2 in the C3:I54 range.

Thanks for your help. I always appreciate it.

Hi!

Try the Array Formula below:

Use Ctrl+Shift+Enter to enter the formula

=LOOKUP(1,1/MMULT(--(L3=C3:I54),TRANSPOSE(COLUMN(C3:I3)/COLUMN(C3:I3))),J3:J54)

Or

=IFERROR(LOOKUP(1,1/MMULT(--(L3=C3:I54),TRANSPOSE(COLUMN(C3:I3)/COLUMN(C3:I3))),J3:J54),"")

Or the normal formula (use Enter to enter the formula) below

=IFERROR(LOOKUP(1,INDEX(1/MMULT(--(L3=C3:I54),{1;1;1;1;1;1;1}),),J3:J54),"")


Markmzz