Excel formula vlookup

Question

I need to fill in the column D using the information from column G (all in the first sheet) and the information from column A in the second sheet.

What needs to be returned to the column is the exact information found in the second sheet with the letter.

I have tried using vlookup with extract and other's but I always get N/A returned and im getting nervous.

Below are two screenshots of each sheet and at the bottom you will find an excel file uploaded with example data.

Excel file with example

Answer 1

You can do this with an array. Remember to enter the formula with Ctrl+shift+enter

=INDEX(ActiveCoupons!A2:A9,MATCH(G3,RIGHT(ActiveCoupons!A2:A9,LEN(ActiveCoupons!A2:A9)-1)*1,0))

Answer 2

In 'ActiveCoupons' put in G2 : =RIGHT(A2,LEN(A2)-1)*1

In 'ActiveCoupons' put in H2 : =A2

Expand the formulas to the rest of the columns G,H

In CouponRedemptionResults put in D2: =VLOOKUP(G2,ActiveCoupons!G:H,2,0)

Expand the formula to the rest of the columns D