Help with Vlookup- lookup value range?

What do you want in your lookup if more than one of the items in a row in sheet L9 are in Sheet1?

I want to lookup up if there is any value from c2:k2 that matches a value from sheet 1 and return the matching number from column 1 from the sheet1.

Thanks again for looking at this.

My question is what happens if there's more than one match in a given line? For example in Row 3, both W61409 and W15900 are on the list in Sheet1. Do you want the first one, both, or ... ?

I would say both if possible? Whats your thoughts?

Thanks,

Tj

Haha. It's your spreadsheet, so whatever's most useful to you should be the guiding criteria, not what I think. So I would ask yourself what you plan to do with this information once you have it. If knowing that there's at least one match for a given row is sufficient, then I'd just show the first hit. If you want to know what your options are for each row, then I'd show them all.

This formula will string together however many "hits" are identified in each row, separated by two spaces. It looks messier than it really is (the problem, as you discovered, is that vlookups only work with one input value, not a range). Does this work for you?

=IFERROR("  "&VLOOKUP(C2,Sheet1!$A$2:$B$419,1,0),"")&IFERROR("  "&VLOOKUP(D2,Sheet1!$A$2:$B$419,1,0),"")&IFERROR("  "&VLOOKUP(E2,Sheet1!$A$2:$B$419,1,0),"")&IFERROR("  "&VLOOKUP(F2,Sheet1!$A$2:$B$419,1,0),"")&IFERROR("  "&VLOOKUP(G2,Sheet1!$A$2:$B$419,1,0),"")&IFERROR("  "&VLOOKUP(H2,Sheet1!$A$2:$B$419,1,0),"")&IFERROR("  "&VLOOKUP(I2,Sheet1!$A$2:$B$419,1,0),"")&IFERROR("  "&VLOOKUP(J2,Sheet1!$A$2:$B$419,1,0),"")&IFERROR("  "&VLOOKUP(K2,Sheet1!$A$2:$B$419,1,0),"")

A variant of this (also messy) would be used to identify only the first hit.

Works perfect!! Thank you so much for your help.......

Happy to help. I think they want you to mark this as SOLVED if you're satisfied that you got a workable solution. Good luck!