2 Columns for lookup

[paankadu1]

Hello

I am looking to use the PO & Line number on one sheet to look up on the open PO report and pull over the Vendor, justification and item budget. I have a Vlookup that is working just utilizing the PO # but sometimes POs have different lines so I need it to also look at the line# to pull the correct data
I tried installing the add in but am on a corporate net work that does not allow me to add it.

I want to be able to type in the project, the PO and Line on the top report and then have it look at the 2nd worksheet and populate the vendor, justification and item budget
I am utilizing 365

Thanks in advance for your help
Angie

 

[Alex Blakenburg]

You are saying a 2nd worksheet but your formula indicates it might actually be a different workbook. A different workbook may limit the functions that will work especially if the 2nd workbook is not open.
Are both worksheets in the same workbook ?
Also your current formula is using wildcards, but PO no and Item no should be an exact match, why the wildcards ?

 

[paankadu1]

You are saying a 2nd worksheet but your formula indicates it might actually be a different workbook. A different workbook may limit the functions that will work especially if the 2nd workbook is not open.
Are both worksheets in the same workbook ?
Also your current formula is using wildcards, but PO no and Item no should be an exact match, why the wildcards ?

They are 2 worksheets in the same workbook. I had to use the wild cards due to how the data pulled over from SAP. without them i was getting a NA error. Once I added the wildcards it pulled the data over. The 2nd worksheet is in a table format. It looks like when I copied the data over to a blank workbook to make it generic it altered the formula. Below is the actual formula

=VLOOKUP("*"&$C30&"*",OpenPO[[#All],[Purchasing Document]:[Net price]],9,0)

 

[Alex Blakenburg]

Can you see if you can figure out why you need the wildcard ie are both sides numeric or not ? Does one side have spaces before or after ?
The below will work for an exact match but would need modification or possibly a different function for a wildcard match.
PS: it is after midnight in Australia so I won't look at it again until tomorrow your time.

20210827 XLookup 2 column.xlsx
	A	B	C	D	E	F	G	H	I	J
1
2
3
4	Project	PO	Line #	Vendor	Justification	Item Budget
5
6	XXX/1234	123456789	1		Row 1	2
7	XXX/1234	123456789	2		Row 2	6
8	XXX/1234	123456789	3		Row 3	3
9
10
11
12
13
14
15	Purchasing Document	Item	WBS Element	Document Date	Vendor	Short Text	Qty	Unit	Net Price
16	123456789	1				Row 1			2
17	123456789	2				Row 2			6
18	123456789	3				Row 3			3
19
Sheet1

Cell Formulas
Range	Formula
E6:E8	E6	=XLOOKUP(1,(OpenPO[Purchasing Document]=$B6)*(OpenPO[Item]=$C6),OpenPO[Short Text],"Not Found")
F6:F8	F6	=XLOOKUP(1,(OpenPO[Purchasing Document]=$B6)*(OpenPO[Item]=$C6),OpenPO[Net Price])

 

[paankadu1]

Thank you! I figured out what the problem was and this worked perfectly!

 

[Alex Blakenburg]

Thanks for letting me know. Glad I could help.