VLOOKUP to return specific text based on content of other cell

[bundyanimal]

Hi,

Just trying to figure out how to return a vlookup command after looking up another cell.

Please refer to image / spreadsheet for info.

1. Look at Column A in "SCHEDULE" Tab. There are package numbers that relate to Supply, Subcontract and other packages. I want a formula to look at the text throughout Column A11 onwards and identify whether it has SUP, SUB and other abreviations as per tab called "sheet 1".
2. I have made a range in Tab "sheet 1" which can be used as a Vlookup.
3. What formula would you use if you wanted to return the answer (i.e. as per Tab "sheet 1" row B5 to B9) in column C of the "SCHEDULE" tab.

(i.e. Lookup Column A and identify the abreviations and return the full agreement name in Column C.

Thanks

[johnmpl]

Hi, @bundyanimal!

You can use this formula:
[C11] : =IF(A11="","",LOOKUP(8^5,SEARCH(MID(A11,8,3),Sheet1!A$5:A$9),Sheet1!B$5:B$9))

Blessings!

[bundyanimal]

Excellent,

Thanks johnmpl for the prompt response.

Populated columns and tried it.

Works great.

Thanks for your help.