VLOOKUP/INDEX/?? solution

centurymantra · Sep 14, 2017

I am working on a project with a couple of different sets ofdata that I would love to automate with an Excel formula if possible.<o

></o

>
<o

> </o

>
I have one set of data with two columns like this:<o

></o

>
A B<o

></o

>

1621<o></o>	001<o></o>
1641<o></o>	001<o></o>
1661<o></o>	001<o></o>
1672<o></o>	001<o></o>
1681<o></o>	001<o></o>
3200<o></o>	002<o></o>
3210<o></o>	002<o></o>
3240<o></o>	002<o></o>
1422<o></o>	003<o></o>
1322<o></o>	004<o></o>
1323<o></o>	004<o></o>
1303<o></o>	005<o></o>
1312<o></o>	005<o></o>
1314<o></o>	005<o></o>
1315<o></o>	005<o></o>
1316<o></o>	005<o></o>
1317<o></o>	005<o></o>
1318<o></o>	005<o></o>
1320<o></o>	005<o></o>
6370<o></o>	012<o></o>
6375<o></o>	012<o></o>
6380<o></o>	012<o></o>
6385<o></o>	012<o></o>
6100<o></o>	013<o></o>
5610<o></o>	014<o></o>
5730<o></o>	014<o></o>
6075<o></o>	014<o></o>
6080<o></o>	014<o></o>
6085<o></o>	014<o></o>

<o

> </o

>
I am working with another set of data like this:<o

></o

>
A B C<o

></o

>

004<o></o>	CHRISTINE SMITH<o></o>	000146703<o></o>
011<o></o>	CHRISTINE SMITH<o></o>	000146703<o></o>
012<o></o>	CHRISTINE SMITH<o></o>	000146703<o></o>
014<o></o>	CHRISTINE SMITH<o></o>	000146703<o></o>
712<o></o>	CHRISTINE SMITH<o></o>	000146703<o></o>
702<o></o>	CHRISTOPHER HIGSON<o></o>	001090588<o></o>
716<o></o>	CHRISTOPHER HIGSON<o></o>	001090588<o></o>
724<o></o>	CHRISTOPHER HIGSON<o></o>	001090588<o></o>

<o

> </o

>
Using the second set of data, I need to look at the value incolumn A and find all instances where it occurs in column B of the firstset. Then I want to create entries in athird workbook that ultimately will come up with the following columns. Taking Christine Smith as an example, thiswould be the result:<o

></o

>
<o

> </o

>
A B<o

></o

>
<o

> </o

>

1322<o></o>	000146703<o></o>
1323<o></o>	000146703<o></o>
6370<o></o>	000146703<o></o>
6375<o></o>	000146703<o></o>
6380<o></o>	000146703<o></o>
6385<o></o>	000146703<o></o>
5610<o></o>	000146703<o></o>
5730<o></o>	000146703<o></o>
6075<o></o>	000146703<o></o>
6080<o></o>	000146703<o></o>
6085<o></o>	000146703<o></o>

<o

> </o

>
<o

> </o

>
This is a table with the number in column C of the secondtable with the value of column A in the first set of data in every instancewhere column A in the second data set is found in column B of the first set. Hopefully, this is clear enough that it can be understood. I would LOVE to find a way of automatingthis, but it is a little beyond my grasp of Excel to figure it out.<o

></o

>
Any help on this is much appreciated!<o

></o

>

Aladin Akyurek · Sep 17, 2017

Let A1:B29 of Sheet1 house the first set of data.
Let A1:C8 of Sheet2 house the second set of data.

Row\Col	A	B
1	11
2
3	1322	000146703
4	1323	000146703
5	6370	000146703
6	6375	000146703
7	6380	000146703
8	6385	000146703
9	5610	000146703
10	5730	000146703
11	6075	000146703
12	6080	000146703
13	6085	000146703
14

In A1 of Sheet3 (the results sheet), control+shift+enter, not just enter:

=SUM(IF(ISNUMBER(MATCH(Sheet1!$B$1:$B$29,Sheet2!$A$1:$A$8,0)),1))

In A3 of Sheet3 control+shift+enter, not just enter, and copy down:

=IF(ROWS($A$3:A3)>$A$1,"",INDEX(Sheet1!$A$1:$A$29,SMALL(IF(ISNUMBER(MATCH(Sheet1!$B$1:$B$29,Sheet2!$A$1:$A$8,0)),ROW(Sheet1!$A$1:$A$29)-ROW(Sheet1!$A$1)+1),ROWS($A$3:A3))))

In B3 of Sheet3 just enter and copy down:

=IF($A3="","",VLOOKUP(VLOOKUP($A3,Sheet1!$A$1:$B$29,2,0),Sheet2!$A$1:$C$8,3,0))

centurymantra · Sep 18, 2017

Excellent! Thanks abunch for this formula. I did work withthis, modifying the formula to reflect the actual number of rows in each sheet(471 in sheet 1 and 1138 in sheet 2), but found it was returning incompleteresults. <o

></o

>
For example, I have one person from sheet two:<o

></o

>

Row<o></o>	A<o></o>	B<o></o>	C<o></o>
275<o></o>	500<o></o>	TRACY SMITH<o></o>	001118229<o></o>
276<o></o>	501<o></o>	TRACY SMITH<o></o>	001118229<o></o>
277<o></o>	504<o></o>	TRACY SMITH<o></o>	001118229<o></o>
278<o></o>	505<o></o>	TRACY SMITH<o></o>	001118229<o></o>
279<o></o>	507<o></o>	TRACY SMITH<o></o>	001118229<o></o>
280<o></o>	508<o></o>	TRACY SMITH<o></o>	001118229<o></o>
281<o></o>	509<o></o>	TRACY SMITH<o></o>	001118229<o></o>
282<o></o>	510<o></o>	TRACY SMITH<o></o>	001118229<o></o>
283<o></o>	511<o></o>	TRACY SMITH<o></o>	001118229<o></o>
284<o></o>	512<o></o>	TRACY SMITH<o></o>	001118229<o></o>
285<o></o>	513<o></o>	TRACY SMITH<o></o>	001118229<o></o>

<o

> </o

>
The corresponding data in sheet one that would be referencedwould be in this section of sheet 1:<o

></o

>

Row<o></o>	A<o></o>	B<o></o>
261<o></o>	3150<o></o>	500<o></o>
262<o></o>	3350<o></o>	500<o></o>
263<o></o>	3650<o></o>	500<o></o>
264<o></o>	5250<o></o>	500<o></o>
265<o></o>	5280<o></o>	500<o></o>
266<o></o>	6155<o></o>	500<o></o>
267<o></o>	6250<o></o>	500<o></o>
268<o></o>	6254<o></o>	500<o></o>
269<o></o>	5254<o></o>	501<o></o>
270<o></o>	3352<o></o>	504<o></o>
271<o></o>	5252<o></o>	504<o></o>
272<o></o>	6252<o></o>	504<o></o>
273<o></o>	3351<o></o>	507<o></o>
274<o></o>	5251<o></o>	507<o></o>
275<o></o>	6251<o></o>	507<o></o>
276<o></o>	5250<o></o>	508<o></o>
277<o></o>	5290<o></o>	508<o></o>
278<o></o>	5630<o></o>	508<o></o>
279<o></o>	3331<o></o>	509<o></o>
280<o></o>	5151<o></o>	509<o></o>
281<o></o>	6137<o></o>	509<o></o>
282<o></o>	5110<o></o>	510<o></o>
283<o></o>	5810<o></o>	511<o></o>
284<o></o>	5780<o></o>	512<o></o>
285<o></o>	5800<o></o>	513<o></o>

<o

> </o

>
This is what I get back for this person:<o

></o

>
<o

> </o

>

3331<o></o>	001118229<o></o>
3352<o></o>	001118229<o></o>
5151<o></o>	001118229<o></o>
5252<o></o>	001118229<o></o>
6137<o></o>	001118229<o></o>
6252<o></o>	001118229<o></o>

<o

> </o

>
Analyzing this, it is only returning results for 504 and 509of column C in sheet 1. N Any thoughts on what is happening with this?<o

></o

>
<o

> </o

>
Thanks!<o

></o

>

Aladin Akyurek · Sep 18, 2017

Instead of putting up what you get, you should post the output you need/expect.

centurymantra · Sep 19, 2017

If we were to look at the results and sort by column B, the results I would like to see under Tracy Smith's number of 001118229 would be:

A	B
3150	001118229
3350	001118229
3650	001118229
5250	001118229
5280	001118229
6155	001118229
6250	001118229
6254	001118229
5254	001118229
3352	001118229
5252	001118229
6252	001118229
3351	001118229
5251	001118229
6251	001118229
5250	001118229
5290	001118229
5630	001118229
3331	001118229
5151	001118229
6137	001118229
5110	001118229
5810	001118229
5780	001118229
5800	001118229

Aladin Akyurek · Sep 19, 2017

@centurymantra

Here is the workbook which implements the set up discussed in post #2 : https://www.dropbox.com/s/fm42fp2pay5klfj/centurymantra.xlsx?dl=0.

Hope this helps.

centurymantra · Sep 20, 2017

Excellent! This is incredibly helpful. I look forward to reviewing this.

Cheers!

VLOOKUP/INDEX/?? solution

centurymantra

New Member

Excel Facts

Aladin Akyurek

MrExcel MVP

centurymantra

New Member

Aladin Akyurek

MrExcel MVP

centurymantra

New Member

Aladin Akyurek

MrExcel MVP

centurymantra

New Member

Similar threads

Forum statistics

Share this page

VLOOKUP/INDEX/?? solution

centurymantra

New Member

Excel Facts

Aladin Akyurek

MrExcel MVP

centurymantra

New Member

Aladin Akyurek

MrExcel MVP

centurymantra

New Member

Aladin Akyurek

MrExcel MVP

centurymantra

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock