Two Way Look up

Hi,
Can any one solve this look up query with two conditions please..
thanks in advance

in g2
=INDEX($C$2:$C$2000,MATCH(E4&B2,INDEX($A$2:$A$2000&$B$2:$B$2000,0),0)) format cells as date

ARRAY formula.

=INDEX(C2:C19,MATCH(E4&F4,A2:A19&B2:B19,0))

thanks sir....
can u pl do it from look up or Vlook up please...

Add column helper to the left before Person Type, so you have format column like this:
Column Helper (A)|Person Type(B)|Person Name(C)|Date of Joining(D)

on "Column Helper" A2 add this code and copy down

Please Login or Register  to view this content.

So you can use this vlookup:

Please Login or Register  to view this content.

nope thats the way to do it use index/match(unless you use a helper)

Thanks can it be done without adding, any new column.

Originally Posted by SDCh

Add column helper to the left before Person Type, so you have format column like this:
Column Helper (A)|Person Type(B)|Person Name(C)|Date of Joining(D)

on "Column Helper" A2 add this code and copy down

Please Login or Register  to view this content.

So you can use this vlookup:

Please Login or Register  to view this content.

read post #6 and read the vlookup help file
why not index/match, you probablywont use vlookup again once you know how to use that

Try this array formula**:

=INDEX(C2:C19,MATCH(F4,IF(A2:A18=E4,B2:B19),0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Format as Date

If you insist on making things difficult, you can enter this array formula (use CNTRL SHFT ENTER)

=SUBSTITUTE(VLOOKUP(E4&F4,$A$2:$A$19&$B$2:$B$19:$C$19,2,FALSE), E4,"")+0

What do you have against INDEX/MATCH?

Originally Posted by vjharry

can u pl do it from look up or Vlook up please...

Here's another way using LOOKUP...

=LOOKUP(2,1/((A2:A19=E4)*(B2:B19=F4)),C2:C19)

Format as Date

=LOOKUP(2,1/(A2:A19=E4)/(B2:B19=F4),C2:C19)

This also works

and another
=SUMPRODUCT((A2:A19=E4)*(B2:B19=F4),C2:C19)

Another one for that file: =SUMIFS(C2:C19,A2:A19,E4,B2:B19,F4)

Thanks Tony its done ...
But could you pl let me know what here "2" refers to?
and same also want to know for "1/((A2:A19=E4)*(B2:B19=F4))"

Thanks A LOT

Originally Posted by Tony Valko

Here's another way using LOOKUP...

=LOOKUP(2,1/((A2:A19=E4)*(B2:B19=F4)),C2:C19)

Let's assume this is the data in the range A2:C5...

M...X...20
F...X...30
M...Z...60
F...Y...50

We want to retun the value in C2:C5 that corresponds to F in column A and X in column B.

Using this formula:

=LOOKUP(2,1/((A2:A5=E4)*(B2:B5=F4)),C2:C5)

We get the correct result of 30.

Here's how it works...

E4 and F4 are the lookup values:

E4 = F
F4 = X

In this application the LOOKUP function has 3 arguments:

LOOKUP(lookup_value,lookup_vector,result_vector)

lookup_value = 2
lookup_vector = 1/((A2:A5=E4)*(B2:B5=F4))
result_vector = C2:C5

We want to find the lookup_value of 2 in the lookup_vector and return the corresponding value from the result_vector.

The lookup_vector is made up of an array of calculations from this expression:

1/((A2:A5=E4)*(B2:B5=F4))

When we break that down we get:

(A2=E4) * (B2=F4) = FALSE * TRUE = 0
(A3=E4) * (B3=F4) = TRUE * TRUE = 1
(A4=E4) * (B4=F4) = FALSE * FALSE = 0
(A5=E4) * (B5=F4) = TRUE * FALSE = 0

We then do a division operation on these results:

1/0 = #DIV/0!
1/1 = 1
1/0 = #DIV/0!
1/0 = #DIV/0!

So, the lookup_vector is the array:

{#DIV/0!;1;#DIV/0!;#DIV/0!}

As I said before, we want to find the lookup_value 2 in the lookup_vector {#DIV/0!;1;#DIV/0!;#DIV/0!}.

However, as we can clearly see there is no lookup_value of 2 in the lookup_vector.

In this application, if the lookup_value is greater than every value in the lookup_vector then it will match the last value in the lookup_vector that is less than the lookup_value. The errors will be ignored (unless the lookup_vector contains all errors).

We know that the calculation of the expression:

1/((A2:A5=E4)*(B2:B5=F4))

Will generate an array that contains only 2 possible values, either a 1 or the #DIV/0! error. For that reason we know that the lookup_value of 2 will always be greater than any value in the lookup_vector.

With a lookup_value of 2 the last value in the lookup_vector that is less than 2 is the 1. So, we return the value from the result_vector that corresponds to the 1 in the lookup_vector:

#DIV/0!...C2
1...C3
#DIV/0!...C4
#DIV/0!...C5

C3 = 30

So:

=LOOKUP(2,1/((A2:A5=E4)*(B2:B5=F4)),C2:C5)

=30

Hopefully, that isn't too confusing!

Thanks a Lot

You're welcome!