Two Way Look up

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

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

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!