complex match and lookup question

[superdaw]

Trying to correct variations in a data set.
Some values are inconstant.
Have a table that reflects a "true" value and all associated variations (in different columns)
Want a formula that will look at values in original data set, match them to a variation in the associated mapping table, and then pull through the associated "true" value.

I've tried to reflect this in attachment.

Any help would be appreciated

[samba_ravi]

j2=IF($D2="","",INDEX(Mapping!$A:$A,AGGREGATE(15,6,ROW(Mapping!$B$2:$E$5)/(Mapping!$B$2:$E$5=D2),1)))

Please Login or Register  to view this content.

Try this formula, copy and paste towards down

[XLent]

edit: repetition of above...

assuming XL2010, per profile, one option:

Please Login or Register  to view this content.