HLOOKUP HELP

[Nova1979]

Hello all
I need some help with a suitable formula for a look up. HLOOKUP I think
I need Trial G3 and I3 to search rows in WMS REPORT C:C and M:M. When the row data matches return WMS REPORT K:K into Trial Q3
I have provided what the result should be in trials Q

Trial sheet:

TRIAL 7 SHOP SPREADSHEET.xlsx
	G	H	I	J	K	L	M	N	O		Q
1
2											13/05/2020
3	AABDB80103	013448741	28-Apr-20	06-May-20	06-May-20	08/05/2020					80591166
4	AABDH80129	015839254	28-Apr-20	06-May-20	06-May-20	08/05/2020					80747337
5	AABDH80129	661628665	12-May-20	12-May-20	18-May-20						80747337
6	AABDH80129	014343749	22-Apr-20	24-Apr-20	11-May-20						80747337
7	AABDE79713	007193997	20-Apr-20	21-Apr-20	01-May-20	04/05/2020	11/05/2020				80541236
8	AABDB79958	661448182	27-Apr-20	27-Apr-20	04-May-20	05/05/2020			12-May-20		80544216
Sheet1

WMS REPORT:

TRIAL 7 SHOP SPREADSHEET.xlsx
	C	D	E	F	G	H	I	J	K	L	M
1
2	AABDB80103	10	abcd					80591166	80591166		013448741
3	AABDH80129	10	abcd					80747337	80747337		015839254
4	AABDH80129	20	abcd					80747337	80747337		661628665
5	AABDH80129	30	abcd					80747337	80747337		014343749
6	AABDE79713	10	abcd					80541236	80541236		007193997
7	AABDB79958	10	abcd					80544216	80544216		661448182
WMS REPORT

In addition to this, I also need help with an IF formula to match Trial Q3 in WMS REPORT K:K and return the matching row WMS REPORT U:U cell data to wherever I place the formula in Trials sheet

Thank you for any and all help

 

[Lexcon07]

I'm not sure if I am misunderstanding your question, but I think the following formula in cells Q3 and dragged to Q8, would produce what you are looking for.

=+VLOOKUP($A3,'WMS REPORT'!$C$2:$M$7,8,0)

With respect to you second question, I think this should work.

=IF($Q3=VLOOKUP($A3,'WMS REPORT'!$C$2:$M$7,9,0),VLOOKUP($A3,'WMS REPORT'!$C$2:$U$7,19,0),"")

 

[Nova1979]

Lexcon,
I adjusted the first formula slightly and it works well. =VLOOKUP($H3,'WMS REPORT'!$C$2:$M$1500,8,0)
I have tried to adjust the second formula without much success
I need Trial X3 to match WMS REPORT K:K and return WMS REPORT U:U to Trial O3
This will then be duplicated for Trial R3, S3 and T3 to reference WMS REPORT V, Y and Z respectively

Here is a XL2BB sample of the end result for Trials sheet.

TRIAL 7 SHOP SPREADSHEET (1).xlsx
	Q	R	S	T	U		W	X
1	PICKED	PICK DATE	ISSUED	ISSUE DATE	Delivered		Date	ERP NUMBER
2							13/05/2020
3	Completed	6/11/2019	Not Started	N/A	11-May-20			80851668
4	N/A	N/A	N/A	N/A	11-May-20			N/A
5	Completed	3/03/2020	Not Started	N/A	11-May-20			80855489
6	Completed	3/03/2020	Not Started	N/A	11-May-20			80836766
7	Completed	10/10/2019	Completed	10/10/2019				80831769
8	Completed	14/10/2019	Completed	14/10/2019	12-May-20			80840821
9	Completed	14/10/2019	Completed	14/10/2019				80855492
Sheet1

Here is a sample for the column refs in WMS REPORT

TRIAL 7 SHOP SPREADSHEET (1).xlsx
	K	L									U	V	W	X	Y	Z
1	Original ERP Document	Activity Area									Picking Status	Actual Pick Date	Staging Status	Shortage Staging Qty	Goods Issue Status	Actual Goods Issue Date
2
3	80591166	M659									Completed	6/11/2019	B	1	Completed	6/11/2019
4	80747337	S002									Completed	3/03/2020	B	1	Completed	3/03/2020
5	80747337	S002									Completed	3/03/2020	B	8	Completed	3/03/2020
6	80747337	S002									Completed	3/03/2020	B	8	Completed	3/03/2020
7	80541236	M159									Completed	10/10/2019	B	1	Completed	10/10/2019
8	80544216	M159									Completed	14/10/2019	B	1	Completed	15/10/2019
9	80546258	M159									Completed	14/10/2019	B	105	Completed	15/10/2019
WMS REPORT

 

[Lexcon07]

Is your data in different columns than shown in the first XL2BB images above? If it isn't, I'm not sure how you updated Formula 1 is working.

=VLOOKUP($H3,'WMS REPORT'!$C$2:$M$1500,8,0).

If your looking got "H3" in the Range C2:M1500, based on what I see in your images, it would return an error. Is it supposed to be "G3"?

BTW if you update your formula to include the IFERROR, function your "N/A" will disappear. (i.e. =IFERROR(VLOOKUP($H3,'WMS REPORT'!$C$2:$M$1500,8,0),"")

I need some clarification in your second request.

• "I need Trial X3 to match WMS REPORT K:K and return WMS REPORT U:U to Trial O3"

Are you only wanting to compare "Trial X3" to the Column K in the "WMS Report"? i.e. Returning a single value? If so, I think a simple VLOOKUP would work

=VLOOKUP($X$3,'WMS REPORT'!$K$2:$U$1500,11,0).

• "This will then be duplicated for Trial R3, S3 and T3 to reference WMS REPORT V, Y and Z respectively"

If I understand correctly, you want to return the Column U (i.e. "Picking Status") value for whichever row matches "R3" (i.e. "6/11/2019") and that result will be in cell "O3"
=VLOOKUP($R$3,'WMS REPORT'!$V$2:$V$1500,11,0).
=VLOOKUP($S$3,'WMS REPORT'!$K$2:$Y$1500,11,0).
=VLOOKUP($T$3,'WMS REPORT'!$K$2:$Z$1500,11,0).

 

[Lexcon07]

Sorry, accidentally uploaded before I was ready and I ran out of edit time, so please disregard my last post...

Is your data in different columns than shown in the first XL2BB images above? If it isn't, I'm not sure how your updated Formula 1 is working.

=VLOOKUP($H3,'WMS REPORT'!$C$2:$M$1500,8,0).

If you're looking for "H3" in the Range C2:M1500, based on what I see in your examples, it would return an error. Is it supposed to be "G3"?

BTW if you update your formula to include the IFERROR, function the "N/A"s will disappear. (i.e. =IFERROR(VLOOKUP($H3,'WMS REPORT'!$C$2:$M$1500,8,0),"")

I need some clarification in your second request.

• "I need Trial X3 to match WMS REPORT K:K and return WMS REPORT U:U to Trial O3"

Are you only wanting to compare cell "Trial X3" (i.e. "80851668") to the contents of "WMS Report Column K"? i.e. Returning a single value from "WMS Report Column U" that corresponds to "Trial X3"? If so, a single VLOOKUP in "Trial O3" should work:

=VLOOKUP($X$3,'WMS REPORT'!$K$2:$U$1500,11,0).

or/

Are you saying you want to compare the contents of "Trial Column X" (i.e. "ERP Number") against the contents of "WMS Report Column K" and return the contents of "WMS Report Column U"? If so, this should be your formula in "Trial O3" and then dragged down to the end of your data set.

=VLOOKUP($X3,'WMS REPORT'!$K$2:$U$1500,11,0).

• "This will then be duplicated for Trial R3, S3 and T3 to reference WMS REPORT V, Y and Z respectively"

Your answer to the first part will provide some clarity to the second part, but I am still unclear what you want to formula to return if "Trial Cell R3/Column R" matches "WMS Report Column V". Do you want it to return "WMS Report Column U" or do you want it to return some other value?

 

[Nova1979]

The last 2 XL2BB images are the exact columns where the data is located.

Formula 1
The reason this does not return an error is because I can use G3, or H3. I was originally thinking I would need to use both G3 and H3 to return the required result which is why I was thinking of a HLOOKUP as I am looking multiple information across the rows.
I adjusted it to IFERROR with a negative return of N/A to make things clearer for others that will be viewing the information

Formula 2
=VLOOKUP($X3,'WMS REPORT'!$K$2:$U$1500,11,0)
I have applied this to Trial column Q with correct results. This returns the correlating result from WMS REPORT U into Trial Q3

This formula is obviously then copied down the column

To clarify (hopefully), =VLOOKUP(X3,'WMS REPORT'$K......... is were it is looking.
This needs to return the correlating pick date in WMS REPORT column V to Trial R3
I require the same search to return WMS REPORT column Y (Goods Issue Status) to return result to Trial S3
I require the same search to return WMS REPORT column Z (Actual Goods Issue Date) to return result to Trial T3

I apologise if I haven't made things clear. I understand the difficulties in helping without knowing all the information being dealt with

Thanks again

 

[Lexcon07]

I'm still not sure how you can use either "Trial Column G or H" as the Lookup Value for the range "WMS REPORT'!$C$2:$M$1500". without returning an error. The VLOOKUP function looks for the Lookup Value in the left most column of the "Lookup Range" (i.e. "WMS Report Column C" or the "AABD..." Code from image 2 in your original post). The values in "Trial Column G" (i.e. "013448741" from image 1 of your original post) will never find a match in Column C. I cannot see "Trial Columns G & H" or "WMS Report Column C" in the most recent XL2BB images, so I will just assume that something has changed in your data from the first two XL2BB images. It works, that's all that matters...

Now on to the second problem.

I think I understand what you are trying to do now...

You are using the same look up variable (i.e. "Trial Column X") to find the data from "WHS Report Column V", "WHS Report Column Y" and "WHS Report Column Z". You want these values shown in "Trial Columns R, S & T" respectively.

Enter this formula in "Trial R3"

=VLOOKUP($X3,'WMS REPORT'!$K$2:$Z$1500,12,0)

Enter this formula in "Trial S3"

=VLOOKUP($X3,'WMS REPORT'!$K$2:$Z$1500,15,0)

Enter this formula in "Trial T3"

=VLOOKUP($X3,'WMS REPORT'!$K$2:$Z$1500,16,0)

Hope this is what you are looking for.

 

[Nova1979]

Looks to have done the trick

Thank you very much

 

[Lexcon07]

No problem, happy to have helped.