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!

 

[GA3]

Sorry Peter... yes...

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

Supposedly but the source data is out of my control

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

 

[Aladin Akyurek]

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

Let A2 of Sheet2 house a UPC code of interest you want to look up in Sheet1.

Let C1:C300 of Sheet1 house the UPC codes, J1:J300 the brands...

In B2 of Sheet2 enter:

=VLOOKUP(A2,Sheet1!$C$1:$J$300,8,0)

or...

=VLOOKUP(A1,CHOOSE({1,2},Sheet1!$C$1:$C$300,Sheet1!$J$1:$J$300),2,0)

Does this help? If not, try to post 10 rows of data from Sheet1...

 

[Peter_SSs]

Sorry Peter... yes...

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

Supposedly but the source data is out of my control

In that case, assume the UPC codes are in A1:H300 of Sheet2, brands are in J1:J300 of Sheet2 and the UPC code of interest is in cell A1 of Sheet1, try this in B1 of Sheet1

=INDEX(Sheet2!$J$1:$J$300,AGGREGATE(15,6,(ROW(Sheet2!$A$1:$A$300)-ROW(Sheet2!$A$1)+1)/(Sheet2!$A$1:$H$300=A1),1))

 

[GA3]

Thanks you! That seems to work! I tried to ask it simply but there is more. I just need to understand your formula so that I can expand it to encompass more upc columns and more data that I need to return to looking up the UPC.

My real example this week has upc codes in A:HBE and the data I need to return to my lookup is in columns HBF:HBK

It is a monster and changes every week.

In that case, assume the UPC codes are in A1:H300 of Sheet2, brands are in J1:J300 of Sheet2 and the UPC code of interest is in cell A1 of Sheet1, try this in B1 of Sheet1

=INDEX(Sheet2!$J$1:$J$300,AGGREGATE(15,6,(ROW(Sheet2!$A$1:$A$300)-ROW(Sheet2!$A$1)+1)/(Sheet2!$A$1:$H$300=A1),1))

 

[Peter_SSs]

Thanks you! That seems to work! I tried to ask it simply but there is more. I just need to understand your formula so that I can expand it to encompass more upc columns and more data that I need to return to looking up the UPC.

My real example this week has upc codes in A:HBE and the data I need to return to my lookup is in columns HBF:HBK

It is a monster and changes every week.

No use trying to understand that formula.
For data that size (over 1.5 million UPC codes if 300 rows), this formula - & I suspect any other worksheet formula - will just cause excel to freeze/crash, though I would be happy to be proved wrong.

It may be possible with a vba approach but in having a quick test of a few such methods, they could also be very sluggish. Would a vba approach be acceptable?

About how many UPC values will you be needing to look up in the other worksheet? That is, how many of these formulas would you be using in that other worksheet?

 

[Joyner]

...For data that size (over 1.5 million UPC codes if 300 rows), this formula - & I suspect any other worksheet formula - will just cause excel to freeze/crash...

It may be possible with a vba approach but in having a quick test of a few such methods, they could also be very sluggish. Would a vba approach be acceptable?

Hello Peter and thank you for the formula.
I was dissecting the formula to add to my Workbook of formulas and thinking about the large data set and thought I would try Microsoft’sExcel Fuzzy Lookup Add in to solve this problem. I have used it on large data sets before, using it for keyword searches, because I could never find a good VBA code version for keyword searches. The Fuzzy Lookup workedwell and was fairly efficient, but I never tried it to lookup across multiple columnsas is the issue here and it seems to work pretty well, but I’m not sure about the OPs actual large set.
Any thoughts on the Fuzzy Add in versus VBA for speed / efficiency?
Thanks

P.S. Anyone else having problems posting of the posting sticking words together or dropping characters?

 

[Peter_SSs]

Any thoughts on the Fuzzy Add in versus VBA for speed / efficiency?

I'm afraid I can't offer anything as I have not used the Fuzzy Add-In.

P.S. Anyone else having problems posting of the posting sticking words together or dropping characters?

What browser are you using?
Have you searched the About This Board forum for similar issues raised by others?