Lookup challenge using Excel formulae

[sharshra]

I have to lookup values from a table. I tried different lookups, but they fetch the value of first occurrence only. In my case, it is a bit complicated & is not straight forward. Value that I'm looking for may not be at the first occurrence. Dummy table for illustration only is shown below. Can someone help to find a solution (without VBA) please?

Challenge 1 - I have to find out Name & Account holder from a reference table for each Account # from calling table. Account holder may not be there for all Names. Solution should read Account # in calling table, search all Account # in reference table & fetch the first occurrence of Account holder & corresponding Name.

Account #	Name	Account holder
98765432	testaccount	test
12345678	abc456	first holder

Challenge 2 - Similar to challenge 1. In this case, I need all values found for each Account #.

Account #	Name	Account holder
98765432	testaccount	test
12345678	abc456, abc	first holder, someone

Reference table -

Name	Account #	Account holder
abc123	12345678
abc456	12345678	first holder
abc	12345678	someone
xyz	12345678
xyz789	98765432
testaccount	98765432	test
mno765	98765432

 

[Peter_SSs]

Like this?

sharshra.xlsm
	A	B	C	D
1
2	Table1	Account #	Name	Account holder
3		98765432	testaccount	test
4		12345678	abc456	first holder
5
6
7	Table2	Account #	Name	Account holder
8		98765432	testaccount	test
9		12345678	abc456, abc	first holder, someone
10
11
12	Table3	Name	Account #	Account holder
13		abc123	12345678
14		abc456	12345678	first holder
15		abc	12345678	someone
16		xyz	12345678
17		xyz789	98765432
18		testaccount	98765432	test
19		mno765	98765432
20
Sheet1

Cell Formulas
Range	Formula
C3:C4	C3	=INDEX(FILTER(Table3[Name],(Table3[Account '#]=[@[Account '#]])*(Table3[Account holder]<>""),""),1)
D3:D4	D3	=INDEX(FILTER(Table3[Account holder],(Table3[Account '#]=[@[Account '#]])*(Table3[Account holder]<>""),""),1)
C8:C9	C8	=TEXTJOIN(", ",1,FILTER(Table3[Name],(Table3[Account '#]=[@[Account '#]])*(Table3[Account holder]<>""),""))
D8:D9	D8	=TEXTJOIN(", ",1,FILTER(Table3[Account holder],(Table3[Account '#]=[@[Account '#]])*(Table3[Account holder]<>""),""))

 

[sharshra]

Thanks for that, Peter. When applied, it is throwing syntax error. I'm trying to understand how it works. For example, it should pick account # in table 2, search for name & account holder in table 3 & enter these values in table 2. But I don't see table 2 referred in the formula. Am I missing something?

 

[Peter_SSs]

When applied, it is throwing syntax error.

How have you "applied" it?
You refer several times to what "it" should do. There are four formulas in my response. Which one of them is the "it" you are referring to?

I don't see table 2 referred in the formula.

My understanding is that the only formulas relating to Table2 are the ones in Table2. Since they are in Table2 they do not need to refer to their own table name?

Which, if any, of the results in my mini-sheets are incorrect?

 

[sharshra]

Table 2 will have account #. Other 2 columns (name & account holder) are to be derived from table 3, using account # to link between table 2 & 3.

Yes, I did apply these, but getting syntax error. Screenshot of error with formula in D8 is pasted below for reference.

 

[Peter_SSs]

In post #1 there is a column heading in each table "Account #"
In your image in post #5 the heading appears to be "account#"

The suggested formula was assuming the heading as in post #1

 

[sharshra]

Brilliant!! It works like magic ? Thanks a lot, Peter.

 

[Peter_SSs]

You're welcome.