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!

 

[EvanDef]

Snakehips, It was a silly error on my part not adding ' around RAW. Could this be a geographic difference? After I added the ' before and after RAW it worked. Thanks so much for your help!

 

[EvanDef]

Jonmo1, Thank you, this worked as well with the exception of changing D1 to D2 for headers and #N/A is returned for no matching data.

 

[Snakehips]

Snakehips, It was a silly error on my part not adding ' around RAW. Could this be a geographic difference? After I added the ' before and after RAW it worked. Thanks so much for your help!

ED,

Glad it got sorted.

As far as I am aware it is only necessary to enclose a sheet name with single quotes if the sheet name contains spaces.
I certainly did not need the '' s in my workbook.

I've known an 'invisible' leading or trailing space on the sheet tab cause an issue but I doubt that you have either or you would need to reference the sheet as ' RAW'! or 'RAW '