Simple question: Can I a lookup across multiple columns? (ie, if c and d = j and k)

  • Thread starter Thread starter LordJezo
  • Start date Start date
L

LordJezo

Say I want to match a value from column a and column b with a value tha
is somewhere in column j and column k, and if it does make the value o
the selected cell the value in column l.

Ex:

A B C J K L
1 2 2 3 x
4 5 6 7 y
8 9 1 2 z

In that example the lookup would give back the value of z for the firs
c column entry and nothing for the others since 4 and 5, and, 8 and
do not match up with any two columns in j and k
 
Something like this might work for you:

I put the table in A1:L3.

I put 1 in A10 and 2 in B10 and this in C10:

=INDEX($L1:$L3,MATCH(A10&CHAR(1)&B10,$A$1:$A$3&CHAR(1)&$B1:$B$3,0))

But instead of just hitting enter, hit ctrl-shift-enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

(and adjust your ranges accordingly.)
 
This is a *half* working formula !
Enter in C1 and drag down to copy.

=IF(ISNA(MATCH(A1,$J$1:$J$100,0)),"",IF(VLOOKUP(A1,$J$1:$L$100,2,0)=B1,VLOOK
UP(A1,$J$1:$L$100,3,0),""))

It *will* work as long as:
<<FOLLOW THIS CAREFULLY>>:
There are no duplicate numbers in column J,
That match a number in column A.

In other words, if column A contains a 55, you cannot have more then one 55
in column J !

That's because the lookup will only pick the *first* 55 it finds in column
J.

Of course, if there are no 55's in column A, you can have any amount of 55's
in column J, since a match would not be possible anyway.

Sorry, but that's the best I could do.

Let's see if someone else can come up with exactly what you need.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

Say I want to match a value from column a and column b with a value that
is somewhere in column j and column k, and if it does make the value of
the selected cell the value in column l.

Ex:

A B C J K L
1 2 2 3 x
4 5 6 7 y
8 9 1 2 z

In that example the lookup would give back the value of z for the first
c column entry and nothing for the others since 4 and 5, and, 8 and 9
do not match up with any two columns in j and k.
 
Back
Top