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!
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also can you post some sample data to the board.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
 
Upvote 0
Thanks for the info, Mod! I've updated my account details and installed the add-in. Is it possible to delete this thread and I can make a newer clean version? In any case, I've pasted my mini sheets below:

Sheet1
Book1.xlsx
AB
1IDDoc Ref
2000000000001#N/A
3000000000002#N/A
4000000000003#N/A
5000000000004#N/A
6000000000005#N/A
Sheet1
Cell Formulas
RangeFormula
B2:B6B2=INDEX(Sheet2!$A$2:$H$6,MATCH(Sheet1!A2,Sheet2!$A$2:$H$6,0)*8)


Sheet2
Book1.xlsx
ABCDEFGH
1ID1ID2ID3ID4ID5ID6ID7Doc Ref
2000000000009000000000008000000000001000000000007000000000006000000000019000000000018AAA
3000000000017000000000016000000000017000000000018000000000019000000000020000000000002EEE
4000000000020000000000021000000000022000000000023000000000004000000000024000000000025ZZZ
5000000000026000000000027000000000005000000000027000000000028000000000029000000000030XXX
6000000000031000000000032000000000033000000000034000000000035000000000036000000000003TTT
Sheet2
 
Upvote 0
Ok, how about
Excel Formula:
=INDEX(Sheet2!$H$2:$H$6,AGGREGATE(15,6,(ROW(Sheet2!$H$2:$H$6)-ROW(Sheet2!$H$2)+1)/(Sheet2!$A$2:$G$6=A2),1))
 
Upvote 0
Ok, that literally worked exactly the way I asked and solved about 95% of my issue--I'm blown away! Thank you so much! I'm also frustrated at myself for missing something about my data that I wish I included from the beginning in this thread. Some IDs from Sheet1 appear in multiple instances in the Sheet2 array, and I need every subsequent instance after the first to also pull back into Sheet1. I do still need the search to proceed the same way. Reporting the subsequent instances as something like, "AAA | ZZZ | XXX" would be perfect.

I have altered the dummy data in a way that captures this reality that I regret neglecting originally. If anyone is able to assist, that would be amazing. And if not, all I have to say is Fluff is amazing haha.

Sheet1
Book1.xlsx
AB
1IDDoc Ref
2000000000001AAA
3000000000002EEE
4000000000003TTT
5000000000004ZZZ
6000000000005XXX
Sheet1
Cell Formulas
RangeFormula
B2:B6B2=INDEX(Sheet2!$H$2:$H$6,AGGREGATE(15,6,(ROW(Sheet2!$H$2:$H$6)-ROW(Sheet2!$H$2)+1)/(Sheet2!$A$2:$G$6=A2),1))


Sheet2
Book1.xlsx
ABCDEFGH
1ID1ID2ID3ID4ID5ID6ID7Doc Ref
2000000000009000000000008000000000001000000000007000000000006000000000019000000000018AAA
3000000000017000000000001000000000017000000000018000000000019000000000020000000000002EEE
4000000000020000000000021000000000022000000000023000000000004000000000001000000000025ZZZ
5000000000004000000000027000000000005000000000027000000000028000000000029000000000030XXX
6000000000031000000000032000000000033000000000034000000000035000000000036000000000003TTT
Sheet2
 
Upvote 0
Could you have the same ID twice on the same row?
If so what would be the expected answer?
 
Upvote 0
No, the same ID will never appear more than once in the same row. In order to explain why that constraint exists, I will need to change my dummy data around a little bit to bring in a few more layers of reality, which I'm more than willing to do if needed, and can do so without compromising anything proprietary.
 
Upvote 0
Ok, if they only appear once, how about
Excel Formula:
=TEXTJOIN(" | ",,IF(Sheet2!$A$2:$G$6=A2,Sheet2!$H$2:$H$6,""))
 
Upvote 0
Solution
It works in the dummy data but getting #N/A error for all records in the real data... I triple-checked all my formula references were pointing at the right cells, but no dice in the end. Does it matter that the real life data represented by Sheet1 column A and Sheet2 columns A-G are actually housing formulae themselves? They are concatenations and vlookups that have not been copy-pasted to special values.
 
Upvote 0

Forum statistics

Threads
1,223,910
Messages
6,175,320
Members
452,635
Latest member
laura12345

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
Back
Top