Searching a range to see if range "contains" a value

Question

I have a huge spreadsheet that I am trying to make more efficient for searching, etc. instead of just using Ctrl+F.

What I would like to accomplish is look in column G to see if a value is contained in that column and if true, reply with the corresponding row value in column U. Notice that the lookup range is not column A, so VLOOKUP would not work.

(Actually Ideally I would like to return both G and U, concatenated together, but I don't know if that's possible)

Here is a simplified example (its school program codes)

Column G (program code - this is what I want to search for) PHD-PPA-XX PHD-PPA-CJPY PHD-PPA-PBAD PHD-PPA-PBPY

Column U (program name - overly simplified for this example) PhD Program 1 PhD Program 2 PhD Program 3 PhD Program 4

I would like to search in column G to see if the program code CONTAINS "PBPY" and if it does the return the program name "PhD Program 4"

My ideal scenario would be the return result would concatenate the two into "PHD-PPA-PBPY - PhD Program 4"

Thanks for the help!

Answer 1

This worked for me

• =INDEX(G1:G4,MATCH("*PBPY",G1:G4,0))&" - "&INDEX(U1:U4,MATCH("*PBPY",G1:G4,0))

By the way, the problem with VLookup is not so much that your lookup value is not in column A, since column G is to the left of column U. The problem lies with looking up a partial string and therefore needing to use a wildcard.

Hope this helps.