Some Sort Of VLookup/Match Formula

[Dazzawm]

I have sheet 1 as below. What I need is similar to a VLookup where on sheet 2 it will give me whats in column B on sheet 1 when the corresponding numbers in column A are found. Problem is VLookup only returns one result for each number.

Sheet 1

Number	Number1
M14307100294003	M279
M14307100294003	M280
M14307100294003	M281
M14307100294003	M282
M14307100294003	M283

Sheet 2 Using VLookup

Number	Number1
M14307100294003	M279
M14307100294003	M279
M14307100294003	M279
M14307100294003	M279
M14307100294003	M279

Obviously on sheet 2 I would like the same result that is on sheet 1. There will be 10s of 1000s of rows and any different amount of matching numbers in column A.
Thanks.

 

[Dazzawm]

You need to reference the sheet(s) where the data is located for this to happen. Look at how it happens in regular Vlookup and adapt.

VLOOKUP across multiple sheets in Excel with examples

Formula examples to Vlookup from another worksheet or workbook, Vlookup in multiple sheets, and indirect Vlookup to dynamically pull data from several worksheets into different cells.

www.ablebits.com

Sorry I am lost! And you say select range but I will be using on different files and different amount of rows

 

[Marcelo Branco]

Maybe something like...

Pasta2
	A	B
1	Number	Number1
2	M14307100294003	M279
3	M14307100294003	M280
4	M14307100294003	M281
5	M14307100294003	M282
6	M14307100294003	M283
Sheet2

Cell Formulas
Range	Formula
B2:B6	B2	=INDEX(Sheet1!$B$2:$B$1000,AGGREGATE(15,6,(ROW(Sheet1!$B$2:$B$1000)-ROW(Sheet1!B$2)+1)/(Sheet1!A$2:A$1000=A2),COUNTIF(A$2:A2,A2)))

Hope this helps

M.

 

[Dazzawm]

Sorry I am lost!

Maybe something like...

Pasta2
	A	B
1	Number	Number1
2	M14307100294003	M279
3	M14307100294003	M280
4	M14307100294003	M281
5	M14307100294003	M282
6	M14307100294003	M283
Sheet2

Cell Formulas
Range	Formula
B2:B6	B2	=INDEX(Sheet1!$B$2:$B$1000,AGGREGATE(15,6,(ROW(Sheet1!$B$2:$B$1000)-ROW(Sheet1!B$2)+1)/(Sheet1!A$2:A$1000=A2),COUNTIF(A$2:A2,A2)))

Hope this helps

M.

Thanks that seems to be better. What if I want to put the formula in column U on sheet 2 and the column A data on sheet 1 is in column K on sheet 2? Sorry I thought it would be easy to adapt once I got formula but its confusing me.

 

[Marcelo Branco]

I did not understand...
Sheet1
Where exactly are your data on Sheet1? Columns A and B?
Sheet2
In which column are the values to be searched?
Do you want the formula in column U?

M.

 

[Dazzawm]

I did not understand...
Sheet1
Where exactly are your data on Sheet1? Columns A and B?
Sheet2
In which column are the values to be searched?
Do you want the formula in column U?

M.

Sorry I have confused myself and got it around the wrong way. Columns A and B are actually on sheet 2 and the result should be on sheet 1. So sorry. So in column A and B on sheet 2 you have as below.

M10501300000098	M279
M10501300000098	M280
M10501300000098	M281
M10501300000098	M282
M10501300000098	M283
M10501300000098	M284
M10501300000100	M285
M10501300000100	M286
M10501300000100	M287
M10501300000100	M288
M10501300000100	M289
M10501300000100	M290
M10501300000101	M291
M10501300000101	M292
M10501300000101	M293
M10501300000101	M294
M10501300000101	M295
M10501300000101	M296

This is sheet 1

Column K	Column U
M10501300000098
M10501300000098
M10501300000098
M10501300000098
M10501300000098
M10501300000098
M10501300000100
M10501300000100
M10501300000100
M10501300000100
M10501300000100
M10501300000100
M10501300000101
M10501300000101
M10501300000101
M10501300000101
M10501300000101
M10501300000101

So on sheet 1 I need the result in column U.

 

[Dazzawm]

Also as mentioned before I will use on different files with different amounts of rows. Rather than a range in the formula can I not just put in row 2 and fire down?

 

[Marcelo Branco]

Try

Pasta2
	K	L	M	N	O	P	Q	R	S	T	U
1	Number										Number1
2	M10501300000098										M279
3	M10501300000098										M280
4	M10501300000098										M281
5	M10501300000098										M282
6	M10501300000098										M283
7	M10501300000098										M284
8	M10501300000100										M285
9	M10501300000100										M286
10	M10501300000100										M287
11	M10501300000100										M288
12	M10501300000100										M289
13	M10501300000100										M290
14	M10501300000101										M291
15	M10501300000101										M292
16	M10501300000101										M293
17	M10501300000101										M294
18	M10501300000101										M295
19	M10501300000101										M296
Sheet1

Cell Formulas
Range	Formula
U2:U19	U2	=INDEX(Sheet2!B$2:B$1000,AGGREGATE(15,6,(ROW(Sheet2!B$2:B$1000)-ROW(Sheet2!B$2)+1)/(Sheet2!A$2:A$100=K2),COUNTIF(K$2:K2,K2)))

M.

 

[Dazzawm]

Try

Pasta2
	K	L	M	N	O	P	Q	R	S	T	U
1	Number										Number1
2	M10501300000098										M279
3	M10501300000098										M280
4	M10501300000098										M281
5	M10501300000098										M282
6	M10501300000098										M283
7	M10501300000098										M284
8	M10501300000100										M285
9	M10501300000100										M286
10	M10501300000100										M287
11	M10501300000100										M288
12	M10501300000100										M289
13	M10501300000100										M290
14	M10501300000101										M291
15	M10501300000101										M292
16	M10501300000101										M293
17	M10501300000101										M294
18	M10501300000101										M295
19	M10501300000101										M296
Sheet1

Cell Formulas
Range	Formula
U2:U19	U2	=INDEX(Sheet2!B$2:B$1000,AGGREGATE(15,6,(ROW(Sheet2!B$2:B$1000)-ROW(Sheet2!B$2)+1)/(Sheet2!A$2:A$100=K2),COUNTIF(K$2:K2,K2)))

M.

I just get a result of #NUM! is the file I am using on too big?

 

[Dazzawm]

With a bit of fiddling I got it to work. Will I have to change range on every file I use it on? And can it be made in to a UDF please?