Return value from cell when lookup data is in an array

[GA3]

I have a lot of UPC codes that are listed in an array A1:H300. In Column J is a product's brand name associated with the UPC codes from that row. I am trying to return the brand name in another workbook by looking up the UPC from the array. I just can not figure out how to do it and I apologize for not knowing what to call this if the post already exists.

Please help.
Thanks!

 

[Special-K99]

Try

=VLOOKUP(Sheet1!A1,Sheet2!A1:J300,10,0)

Where Sheet1!A1 is the UPC code

Note I've extended the range to A1:J300 to use the VLOOKUP formula.
Sheet2!A1:J300 is the range on another sheet.
I've used another Sheet cos Ive used A1 as both a lookup value and part of the range.
Change Sheet1!A1 to wherever your UPC code is.

 

[GA3]

Thanks Special-K99 but this only works if the upc I am looking for is in column A. Otherwise it returns an #N/A.

The UPC can exist in any of the A1:H300 cells. I need it to read the whole range array to find the UPC and then tell me what is in that row in column J.

 

[Special-K99]

Sorry, but that sounds like a ridiculous layout.
You shouldn't have something important like a lookup code and bury it in the middle of a bunch of other less valueble data. I'm thinking needle in a haystack.

I suspect this is out of your control though.

So what constitutes a UPC ?
If I have a row like

ABC 123 DEF GHI

and DEF is my UPC how would I know that the UPC is not ABC or 123 or GHI ?

 

[Peter_SSs]

Does a particular UPC code only ever appear once in the range A1:H300?

 

[Aladin Akyurek]

Try something like:

=VLOOKUP(lookupvalue,CHOOSE({1,2},UPCrange,J1:J300),2,0)

UPCrange is the range within A1:H300 which houses the UPC codes.

 

[GA3]

Yes, it is out of my control unfortunately.

I am given a file that has product brands in one column with all of their upc's in 1 excel cell. I have no control over that export as it comes from a 3rd party. The only good thing about this is they are comma separated in that cells so I can use "Text to Columns" to split those UPC codes in their own columns. This is what causes the multiple columns and still be located by the data I am still needing to reference. If I separate the data away and put the upc's in 1 column to do a traditional VLOOKUP, then I do not have the Brand data to accompany it (there are other data fields too, I am just trying to simplify the request).

A UPC is the bar code you see on retail products that you purchase. Usually consisting of 10-15 numerical characters.

So I have Brand in a column and 1234567890, 1234567891, 1234567892 etc in columns that are associated with that brand.

This is why I am trying to find the UPC in the array and then reference the column that has the brand name in it when I look it up from another document. I get new files every couple weeks so doing the leg work the long way wouldn't help me as data changes.

I am just left banging my head

 

[GA3]

Supposedly but the source data is out of my control

 

[GA3]

Aladin, How would I use that if the VLOOKUP was on Sheet2 but the data is on Sheet1?

Try something like:

=VLOOKUP(lookupvalue,CHOOSE({1,2},UPCrange,J1:J300),2,0)

UPCrange is the range within A1:H300 which houses the UPC codes.

 

[Peter_SSs]

Are any of your responses the answer to my question?
If so, which one?
If not, could you address that please?