Lookup Value Across 7 Columns, but Go from Top Row to Bottom Row

justtryingtolearn

New Member
Joined
Mar 9, 2018
Messages
11
Office Version
  1. 365
Platform
  1. Windows
Hey everybody, love this site and the helpful vibes everyone brings!

On Sheet1, I have a column of unique strings. On Sheet2, I have 7 columns of unique strings, and an 8th column with data I would like pulled into Sheet1 at the end. Each unique string from Sheet1 is buried somewhere among the 7 columns in Sheet2. I am wanting to lookup each unique string from Sheet1 in the 7 columns of Sheet2, but I am needing the search to proceed from the top row (7 cells wide) to the bottom. This is to say, starting in cell A2, the search pattern would need to go next to B2, all the way to the 7th cell, G2, before dropping down a row and starting again from A3 to G3. When a match is found, I am needing the adjacent data in Sheet2's 8th column, H, pulled adjacent to each unique string on Sheet1.

I tried using a combo of the INDEX and MATCH formulae to achieve my end here, but was unsuccessful, though since I'm a heavy VLOOKUP user and this was my first attempt using the INDEX and MATCH combo method, perhaps I'm just making a simple mistake. Below represents a dummy version of the dataset I'm working with (though it does not explain the reason why I need the search to proceed in the order I need it to proceed--which I still need).

1640118362294.png

1640118402198.png


Whether I'm just misusing the INDEX and MATCH combo, need to use another formula, or I am needing to employ some VBA here, please let me know. I am open to any of these approaches and have limited enough knowledge of VBA that I hopefully can follow along.

Thanks so much--you guys are awesome!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,354
Office Version
  1. 365
Platform
  1. Windows
If the formula is returning #N/A that would suggest you have cells with that error on sheet2
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

justtryingtolearn

New Member
Joined
Mar 9, 2018
Messages
11
Office Version
  1. 365
Platform
  1. Windows
Ah! Thank you! I addressed the #N/As and it works! I can't tell you enough how much I appreciate your generous help--I've learned a TON in under 24 hours.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,400
Messages
5,837,024
Members
430,467
Latest member
FrazzledbyExcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top