welcome, Faraz. glad it was of help to you, too.

.. if the two columns do not match can I have a function
to lookup in corresponding column D or E
and return a value randomly from any instead of returning an #N/A?

Assuming col D in X contains the values to be returned if there's no match,
you could try this, array-entered as befo

=IF(OR(A1="",B1=""),"",IF(ISNA(MATCH(1,(X!A$1:A$10 0=A1)*(X!B$1:B$100=B1),0)),INDEX(X!D$1:D$100,randb etween(1,100)),INDEX(X!C$1:C$100,MATCH(1,(X!A$1:A$ 100=A1)*(X!B$1:B$100=B1),0))))

Note that RANDBETWEEN requires the Analysis Toolpak* be installed and
activated. Check the "Analysis Toolpak" box via Tools Add-Ins. Chip
Pearson's page has details on the ATP at:
http://www.cpearson.com/excel/ATP.htm

.. why did you place "1" as lookup value in Match function?

Because the lookup array in the MATCH, this part:
(X!A$1:A$100=A1)*(X!B$1:B$100=B1)
will resolve to an array of ones/zeros, eg: {0;0;1;0;0;0;0;...}
depending on where the dual criteria is satisfied (1's) or not (0's)

Using lookup value: 1 in MATCH would hence give us the (1st) matching
position within the array where the dual criteria is satisfied. It's presumed
of course, that there should be only a single matching position to be
returned within the array.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"FARAZ QURESHI" wrote:
THAT WAS GREAT!!!!

Max u didn't only solve Mike's but one of my very own problem as well.
However, I have a little different one i.e. if the two columns do not match
can I have a function to lookup in corresponding column D or E and return a
value randomly from any instead of returning an #N/A?

By the way sure am anxious to know why did you place "1" as lookup value in
Match function?

Thanx again buddy!