IF/AND, VLOOKUP, MATCH, tried them all

[EvanDef]

Hello Forum Experts!

I have a dillemma I hope someone may be able to help solve.

I have 2 sheets in the same workbook, RAW and Defects. Both sheets have mostly the same Headers but not in the same order. On the Defects sheet, I am trying to populate an 'Order Status' from the RAW sheet if Case numbers match between the two sheets. One thing to consider, there could be multiple Case No.'s in the defects sheet that match the RAW sheet.

Here is an example:
RAW Sheet[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Column I[/TD]
[TD]Column L[/TD]
[/TR]
[TR]
[TD]Order Status[/TD]
[TD]Case No.[/TD]
[/TR]
[TR]
[TD]Closed[/TD]
[TD]234-13-108921.01[/TD]
[/TR]
[TR]
[TD]Pending[/TD]
[TD]002-14-111961.01[/TD]
[/TR]
[TR]
[TD]Cancelled[/TD]
[TD]006-14-106841.01[/TD]
[/TR]
[TR]
[TD]Closed[/TD]
[TD]014-14-102681.02[/TD]
[/TR]
[TR]
[TD]Pending[/TD]
[TD]014-14-124681.01[/TD]
[/TR]
</tbody>[/TABLE]

Defects Sheet
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Column D[/TD]
[TD]Column Y[/TD]
[/TR]
[TR]
[TD]Case No.[/TD]
[TD]Order Status[/TD]
[/TR]
[TR]
[TD]234-13-108921.01[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]234-13-108921.01[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]002-14-111961.01[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]006-14-106841.01[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]006-14-106841.01[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]006-14-106841.01[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]014-14-102681.02[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]014-14-124681.01[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thanks for your time!

 

[Skybluekid]

Try

=INDEX('RAW Sheet'!I:I,MATCH('RAW Sheet'!L1,'DEFECTS Sheet'!D:D,0))

 

[Snakehips]

EvanDef,

I was thinking....

Excel Workbook
	I	J
1	Order Status	Case No.
2	Closed	234-13-108921.01
3	Pending	002-14-111961.01
4	Cancelled	006-14-106841.01
5	Closed	014-14-102681.02
6	Pending	014-14-124681.01
RAW

Excel Workbook
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1	Case No.																					Order Status
2	234-13-108921.01																					Closed
3	234-13-108921.01																					Closed
4	002-14-111961.01																					Pending
5	006-14-106841.01																					Cancelled
6	006-14-106841.01																					Cancelled
7	006-14-106841.01																					Cancelled
8	014-14-102681.02																					Closed
9	014-14-124681.01																					Pending
10	12345-2323																					**No Match**
Defects

Hope that helps.

 

[EvanDef]

Thanks for the reply Skybluekid. Using your formula, I obtained about the same results as what I was trying. About 1/3 of the data returns populated with the right status, a few come back with "0" and the majority are #N/A.

 

[EvanDef]

Hello Snakehips, i noticed your reply after i replied to Skyblue. When i tried your formula, everything came back **No Match**.

 

[EvanDef]

This is what I have tried as well...
=VLOOKUP($D2,'Raw'!$A$1:$L$339,MATCH(Y$1, 'Raw'!$A$1:$L$1,0),0)
Similar results to Skyblue's formula

 

[Snakehips]

Hello Snakehips, i noticed your reply after i replied to Skyblue. When i tried your formula, everything came back **No Match**.

As you can see from my posted example it does return the status.
Have I misinterpreted the layout of your data?

 

[EvanDef]

Column J is Actually Column L, from my example, but I dont think that should make a difference if I simply correct it in my formula. Would the 'Column#' you have listed as "1" make a difference? Reading the formula makes perfect sense and it shoudl work. i am thinking maybe I ommitted a critical detail, but I cant think of it.

 

[Jonmo1]

OK, VLOOKUP is not going to work, because it can't return a value that is 'to the left' of the column the match is found in.
So throw that away..

INDEX/MATCH is the way to go.

Skyblue almost had it, but it looks like the sheet names were reversed in the Match part

=INDEX('RAW Sheet'!I:I,MATCH('RAW Sheet'!L1,'DEFECTS Sheet'!D:D,0))
should be
=INDEX('RAW Sheet'!I:I,MATCH('DEFECTS Sheet'!D1,'RAW Sheet'!L:L,0))

 

[Snakehips]

Sorry for that error.

However, it should work with a change of column reference???

Excel Workbook
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1	Case No.																					Order Status
2	234-13-108921.01																					Closed
3	234-13-108921.01																					Closed
4	002-14-111961.01																					Pending
5	006-14-106841.01																					Cancelled
6	006-14-106841.01																					Cancelled
7	006-14-106841.01																					Cancelled
8	014-14-102681.02																					Closed
9	014-14-124681.01																					Pending
10	12345-2323																					**No Match**
Defects