hyperlink formula using index/match or vlookup?

so i'm trying to do this:

- goto cell A4; look for it in Sheet2, column A;
- then return what that row has in column G, followed by a "," (so as to allow sending an email to multiple recipients) followed by what that row has in column H;
- followed by what is in cell B4, and making that the subject.



i'm trying to work on this, but dont know how to implement either index/match or vlookup aspect
=HYPERLINK(CONCATENATE("mailto:",G3,H3,"?subject=",B4,),"Send e-mail")



appreciate any help!

This will get you what is in column G, H and combine that with B4.

=INDEX(Sheet2!A:H,MATCH(A4,Sheet2!A:A,0),7) & "," &INDEX(Sheet2!A:H,MATCH(A4,Sheet2!A:A,0),8) & "," & B4

I didn't understand the last part.

thanks for replying...

cell b4 should be input as the subject line of the email is what i was trying to say..

Formula's cannot place email subjects. You will have to use macros to do that.

you're wrong. you can place email subjects and even body text with a formula


=HYPERLINK(CONCATENATE("mailto:",C3,C4,C5,"?subject=",D3,"?body=",E3),"Send e-mail")

Anyway, you got how to get the values from different cells using Index & Match. Need help with hyperlink?

considering that the title of the thread is hyperlink formula, what do you think

No, I am not wrong. This formula only places a hyperlink in Excel and you will have to click it to open outlook and place the subject. I am talking about a macro that opens outlook and place subject on it's own.

what're you even talking about?

you click the link and then the subject and body are automatically populated with whatever you indicated in the formula....

you dont need a macro for that

Missed that part, my bad.

i'm trying to work on this, but dont know how to implement either index/match or vlookup aspect

Concentrated on this part earlier.

Embedded Index & Match part in the Hyperlink formula.

=HYPERLINK(CONCATENATE("mailto:", INDEX(Sheet2!A:H,MATCH(A4,Sheet2!A:A,0),7) & "," &INDEX(Sheet2!A:H,MATCH(A4,Sheet2!A:A,0),8),"?subject=",B4,),"Send e-mail")