Lookup a cell in columns that contain only "Part"of that cell

Hi , i am trying to create a look up formula for
Lookup cell A1(JobA:180123) in multiple columns (B, C, D, E & F) on another sheet and return the value in column G(column 7).
My issue is that the other sheet will only have 180123, not the full cell description.
can excel return the value in column G if it finds a partial match for the look up cell(A1) in any of the columns B to F?

1st issue is to get it to find just 180123 in the range given that the lookup ref cell is JobA:180123,
2nd issue is can i make it look up multiple columns for 180123?

Thanks

Welcome to the Board.

References aren't explicit so, in general terms, you could try something like:

Formula:

Please Login or Register  to view this content.

where Sheet1 holds your criteria, and Sheet2 your data
modify range references to Sheet2 rows $1:$1000 to a suitable range but avoid overly large ranges (i.e. $B:$F) if possible.

Thanks Heaps! worked a treat!