Help to pick string value if match happens

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Eddy Stan - 02 May 2008 06:24 GMT

Hi

I have 2 ranges, for convenience i have copied in single sheet.
Range1 is a5:b2500 and Range2 is f5:h2500
now i want a function at c5
Like picking 2 words from a5 & b5 and
comparing all rows from f5:g2500, if they match then pick value in h cell
for example:
a5 = pink shiffon 20% woollen (2 words pink shiffon)
b5 = grand trunk textiles (2 words grand trunk)
match found at f200 & g200 with same words
then pick string at h200 "indent 2000 yards, in 4 sets......."
and place at c5
suppose you have 3 matches still pick the first match.
i need to copy the function from c5 thro c2500
i tried to use wmid() function but still not sure of my results
please help me.
if this not possible by function please give me macro, but I prefer function.
thanks in advance

Reply to this Message

Dave Peterson - 02 May 2008 11:54 GMT

Saved from a previous post:

If you want exact matches for just two columns (and return a value from a
third), you could use:

=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100),0))

(all in one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.

This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.

And you can add more conditions by just adding more stuff to that product
portion of the formula:

=index(othersheet!$d$1:$d$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100)
*(c2=othersheet!$c$1:$c$100),0))

> Hi
>
[quoted text clipped - 15 lines]
> if this not possible by function please give me macro, but I prefer function.
> thanks in advance

Signature

Dave Peterson

Reply to this Message