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

[justtryingtolearn]

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).

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]

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.

 

[justtryingtolearn]

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
	A	B
1	ID	Doc Ref
2	000000000001	#N/A
3	000000000002	#N/A
4	000000000003	#N/A
5	000000000004	#N/A
6	000000000005	#N/A
Sheet1

Cell Formulas
Range	Formula
B2:B6	B2	=INDEX(Sheet2!$A$2:$H$6,MATCH(Sheet1!A2,Sheet2!$A$2:$H$6,0)*8)

Sheet2

Book1.xlsx
	A	B	C	D	E	F	G	H
1	ID1	ID2	ID3	ID4	ID5	ID6	ID7	Doc Ref
2	000000000009	000000000008	000000000001	000000000007	000000000006	000000000019	000000000018	AAA
3	000000000017	000000000016	000000000017	000000000018	000000000019	000000000020	000000000002	EEE
4	000000000020	000000000021	000000000022	000000000023	000000000004	000000000024	000000000025	ZZZ
5	000000000026	000000000027	000000000005	000000000027	000000000028	000000000029	000000000030	XXX
6	000000000031	000000000032	000000000033	000000000034	000000000035	000000000036	000000000003	TTT
Sheet2

 

[Fluff]

Thanks for that.
No need to close the thread, I'll be back shortly.

 

[Fluff]

Ok, how about

=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))

 

[justtryingtolearn]

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
	A	B
1	ID	Doc Ref
2	000000000001	AAA
3	000000000002	EEE
4	000000000003	TTT
5	000000000004	ZZZ
6	000000000005	XXX
Sheet1

Cell Formulas
Range	Formula
B2:B6	B2	=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
	A	B	C	D	E	F	G	H
1	ID1	ID2	ID3	ID4	ID5	ID6	ID7	Doc Ref
2	000000000009	000000000008	000000000001	000000000007	000000000006	000000000019	000000000018	AAA
3	000000000017	000000000001	000000000017	000000000018	000000000019	000000000020	000000000002	EEE
4	000000000020	000000000021	000000000022	000000000023	000000000004	000000000001	000000000025	ZZZ
5	000000000004	000000000027	000000000005	000000000027	000000000028	000000000029	000000000030	XXX
6	000000000031	000000000032	000000000033	000000000034	000000000035	000000000036	000000000003	TTT
Sheet2

 

[Fluff]

Could you have the same ID twice on the same row?
If so what would be the expected answer?

 

[justtryingtolearn]

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.

 

[Fluff]

Ok, if they only appear once, how about

=TEXTJOIN(" | ",,IF(Sheet2!$A$2:$G$6=A2,Sheet2!$H$2:$H$6,""))

 

[justtryingtolearn]

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.