Look up value with more than 1 criteria

[agatina11]

Hi everyone,

I would appreciate your help with a formula to find a value in 1 spreadsheet and pull it into the second one.

Basically, I've got 1 source of data, that both have 2 criteria in common, ie: date and suppliers name ( these would be my criteria). First has also order number and the second has Reference number. I want to link these two, the order number with the reference number ( 1 reference can be linked to more than 1 order)

So want I want is to have reference number linked to the order number if the supplier and and date are the same for both in both spreadsheets.

Example:
1st spreadsheet with data

Supplier	Oder number	Date	Reference
x	12	21.11.2018	this i want to look up
x	13	21.11.2018
x	14	21.11.2018
y	15	21.11.2018
y	16	21.11.2018
y	17	21.11.2018
y	18	21.11.2018
z	19	20.11.2018
z	20	20.11.2018
z	21	20.11.2018
a	22	20.11.2018
a	23	20.11.2018
a	24	20.11.2018

<tbody>
</tbody>

2nd spreadsheet with data

Supplier name	Reference	date
X	AABB	21.11.2018
X	AABB	21.11.2018
X	AABB <colgroup><col width="64" style="width:48pt"> </colgroup><tbody> </tbody>	21.11.2018
Y	AACC <colgroup><col width="64" style="width:48pt"> </colgroup><tbody> </tbody>	21.11.2018
Y	AACC <colgroup><col width="64" style="width: 48pt;"></colgroup><tbody> </tbody>	21.11.2018
Y	AACC <colgroup><col width="64" style="width: 48pt;"></colgroup><tbody> </tbody>	21.11.2018
Y	AACC <colgroup><col width="64" style="width: 48pt;"></colgroup><tbody> </tbody>	21.11.2018
Z	AAPP	20.11.2018
Z	AAPP	20.11.2018
Z	AAPP	20.11.2018
A	AAYY	20.11.2018
A	AAYY	20.11.2018
A	AAYY	20.11.2018

<tbody>
</tbody>

Thanks in advance for your help

Agata

 

[JazzSP8]

There might be a better way than this, but I'd use the information you want to lookup to create a Unique key in a helper column.

So, insert a Column before your data set and join the two bits you need like;

=B1&D1

In both data sets, then you can use your VLOOKUP with that value to get the information you need.

You can hide the helper column if it gets in the way.

 

[taigovinda]

Hi Aqata,

I would do it the same way as JazzSP8. If you don't like the helper column, you could concatenate with choose and array formula (note ctrl-shift-enter, else will give wrong answers):

Sheet1:

	A	B	C	D
1	Supplier	Oder number	Date	Reference
2	x	12	21.11.2018	AABB
3	x	13	21.11.2018	AABB
4	x	14	21.11.2018	AABB
5	y	15	21.11.2018	AACC
6	y	16	21.11.2018	AACC
7	y	17	21.11.2018	AACC
8	y	18	21.11.2018	AACC
9	z	19	20.11.2018	AAPP
10	z	20	20.11.2018	AAPP
11	z	21	20.11.2018	AAPP
12	a	22	20.11.2018	AAYY
13	a	23	20.11.2018	AAYY
14	a	24	20.11.2018	AAYY

<tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula D2 {=VLOOKUP(A2&C2,CHOOSE({1,2},Sheet2!$A$2:$A$14&Sheet2!$C$2:$C$14,Sheet2!$B$2:$B$14),2,0)} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Sheet2:

	A	B	C
1	Supplier name	Reference	date
2	X	AABB	21.11.2018
3	X	AABB	21.11.2018
4	X	AABB	21.11.2018
5	Y	AACC	21.11.2018
6	Y	AACC	21.11.2018
7	Y	AACC	21.11.2018
8	Y	AACC	21.11.2018
9	Z	AAPP	20.11.2018
10	Z	AAPP	20.11.2018
11	Z	AAPP	20.11.2018
12	A	AAYY	20.11.2018
13	A	AAYY	20.11.2018
14	A	AAYY	20.11.2018

<tbody>
</tbody>

Sheet2​

Tai

Edit: screenshots from htmlMaker add-in available here: https://www.mrexcel.com/forum/excel-questions/628649-recommended-add-ins-links.html