How to pull in multiple fields with a VLOOKUP?

[HelpMeWithExcelPlease]

Hi all,

I am trying to write a formula where I can put start with the data in Table 1 and then write a formula in column B of Table 2 to pull in the correct ID numbers from Table 1. Essentially I want to do a VLOOKUP, but I need the VLOOKUP to pull in the data from all rows that meet the criteria, not just the first row.

Table 1
[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Field Name[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]address_number[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]zip_code[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]last_name[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]first_name[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]address_number[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]last_name[/TD]
[/TR]
</tbody>[/TABLE]

Table 2
[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]Field Name[/TD]
[TD]ID[/TD]
[/TR]
[TR]
[TD]address_number[/TD]
[TD]1, 3[/TD]
[/TR]
[TR]
[TD]zip_code[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]last_name[/TD]
[TD]2, 4[/TD]
[/TR]
[TR]
[TD]first_name[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

Any ideas on what formula I can use to do this?

Thank you in advance for any help or direction!

 

[Aladin Akyurek]

Let A1:B7 house the data of Table 1.

Let F:G house Table 2...

In G2 control+shift+enter, not just enter, and copy down:

=TEXTJOIN(", ",TRUE,IF($B$2:$B$7=$F2,$A$2:$A$7,""))

 

[philwojo]

I am not certain, but this UDF might work if you can move your ID field to the right of your Field name in Table 1.

There might be other solutions as well.

https://www.extendoffice.com/docume...eturn-multiple-values-separated-by-comma.html

 

[HelpMeWithExcelPlease]

Let A1:B7 house the data of Table 1.

Let F:G house Table 2...

In G2 control+shift+enter, not just enter, and copy down:

=TEXTJOIN(", ",TRUE,IF($B$2:$B$7=$F2,$A$2:$A$7,""))

Thanks for the quick response! I'm not sure I'm on the same page though - I was more so asking from a standpoint of if I have the information in table 1, how do I go about creating table 2 to appear as it does in my example?

Sorry for the confusion on this, or if I am misunderstanding your solution

 

[HelpMeWithExcelPlease]

Could I use an INDEX function instead? Does that allow for multiple rows to be checked? Ideally I would like my end result to appear as Table 2 shows, with all the IDs associated with the single field name in a single cell rather than split up across multiple.

 

[Aladin Akyurek]

Thanks for the quick response! I'm not sure I'm on the same page though - I was more so asking from a standpoint of if I have the information in table 1, how do I go about creating table 2 to appear as it does in my example?

Sorry for the confusion on this, or if I am misunderstanding your solution

Are you saying that Table 2 does not exist at all?

 

[HelpMeWithExcelPlease]

Yes, Table 2 does not currently exist as shown on the original post. What does exist is column A of Table 2, so all of the field names are listed, but column B where the corresponding ID numbers should go is not currently populated. I am looking for a formula that I can use in column B of table 2 to pull in the correct information from Table 1.

 

[philwojo]

That is what Aladin provided, he made assumptions that your table 2 was in certain cells, if yours is in a different place update it to reflect the new locations.

Di you try and put in the formula, if so what happened?

Did you press CTRL-SHIFT-ENTER after you put the formula in the cell?

 

[Aladin Akyurek]

Yes, Table 2 does not currently exist as shown on the original post. What does exist is column A of Table 2, so all of the field names are listed, but column B where the corresponding ID numbers should go is not currently populated. I am looking for a formula that I can use in column B of table 2 to pull in the correct information from Table 1.

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][/tr][tr][td]

1​

[/td][td]ID[/td][td]Field Name[/td][td][/td][td][/td][td][/td][td]Field Name[/td][td]IDs[/td][/tr]
[tr][td]

2​

[/td][td]

1​

[/td][td]address_number[/td][td][/td][td][/td][td][/td][td]address_number[/td][td]1, 3[/td][/tr]
[tr][td]

3​

[/td][td]

1​

[/td][td]zip_code[/td][td][/td][td][/td][td][/td][td]zip_code[/td][td]1[/td][/tr]
[tr][td]

4​

[/td][td]

2​

[/td][td]last_name[/td][td][/td][td][/td][td][/td][td]last_name[/td][td]2, 4[/td][/tr]
[tr][td]

5​

[/td][td]

3​

[/td][td]first_name[/td][td][/td][td][/td][td][/td][td]first_name[/td][td]3[/td][/tr]
[tr][td]

6​

[/td][td]

3​

[/td][td]address_number[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

7​

[/td][td]

4​

[/td][td]last_name[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

In F2 control+shif+enter, not just enter, and copy down:

=IFERROR(INDEX($B$2:$B$7,SMALL(IF(FREQUENCY(IF(1-($B$2:$B$7=""),MATCH($B$2:$B$7,$B$2:$B$7,0)),ROW($B$2:$B$7)-ROW($B$2)+1),
    ROW($B$2:$B$7)-ROW($B$2)+1),ROWS($F$2:F2))),"")

In G2 control+shift+enter, not just enter, and copy down:

=IF($F2="","",TEXTJOIN(", ",TRUE,IF($B$2:$B$7=$F2,$A$2:$A$7,"")))

See the workbook which implements this set up: https://www.dropbox.com/s/gz6xmndeq813sgz/HelpMeWithExcelPlease%20How%20to%20pull%20in%20multiple%20fields%20with%20a%20VLOOKUP.xlsx?dl=0