Hi ramdubai
As I said in my previous post, if there is more then one value to return, then use the second formula. You will need to change the formula as in the first post you wanted to return a value from the third column.
=IFERROR(INDEX(Sheet1!$A$2:$A$1000,SMALL(IF((Sheet1!$B$2:$B$1000="W-001")*(Sheet1!$C$2:$C$1000="REP"),ROW(Sheet1!$2:$1000)-ROW(Sheet1!$1:$1)),ROW($A1))),"")
Array formula (CTRL + SHIFT + ENTER to commit) and copy down. You will need to adjust your columns, rows and sheet to suit your requirment.
So with your example, there is only 1 match for "W-001" & "REP": shell-3
Kevin
Perhaps something along these lines.
=IF(A1=B1,C1,"")
if i have a column a and in column we have two or more same no.(like ab-230,ab-230),randomaly. i want to search them and add there value.
ex:
A B C
ab-230 2 4
an-25 4 7
ab-230 3 6
we want to add column a like
A b C
ab-230 5 10
pls reply me
I have two an excel with 3 sheets. One sheet has desc column, another sheet has id column and desc column. now I have to compare two descs and fetch the id value in another column.
example
zone1 zone2 id
hyd blr 1 3
hyd hyd 2 1
blr che 3 4
che tec 4 0
if zone1 value wherever occur in the zone2, then corresponding id value has to display in D column .
Please advise.
Regards
Prabhakar