Vlookup with match / index option?

[Bax]

Hi All,

Attached example file has data with which I need to do the following;

On the tab "part list" I put in the part number in column F.
Column C should then be populated automatically by looking up column F in the tab "Lookup" and showing the value found in column B of that tab.

The difficulty with this is, is that when the part number in column F starts with "DN", the lookup value should end with "FD/A" and when the part number in column F starts with "Z", the lookup value should end with "TY/A".

This last part I can't get it to work. I've tried vlookup with a index/match function inside, but it won't work somehow...

Any ideas amongst your experts?

Many thanks
Peter

[etaf]

when you put
DN661-5876
into column F

you have three entries with different values in column B in the lookup , for that value of column F DN661-5876

you need a unique value in column A on lookup - or another value to lookup
the lookup will not know which one of the three to return and so will return the first match

unless i miss- understood

[AL1976]

hi, in C3:
=IF(LEFT(F3;2)="DN";LEFT(VLOOKUP('Part list'!F3;Lookup!$A$2:$B$7;2;FALSE);5)&"FD/A";IF(LEFT(F3;1)="Z";LEFT(VLOOKUP('Part list'!F3;Lookup!$A$2:$B$7;2;FALSE);5)&"TY/A";""))

rgds,
AL

[Bax]

Hi Al,

This is perfect, thanks a million!!

Star earned.

Regards,
Peter