Tricky VLOOKUP

[Lukums]

G'day guys - we have 2 sheets sheet1 and sheet2 both sheets can go down to row 10,000

JOB ID is found in Column C and Delivery Price is found in Column I.

We have the below:

What we found was because Vlookup is finding the first entry with BLANK it returns BLANK as you'd expect - however we need it to skip if it returns BLANK and find the next pairing JOB ID.
There's plenty of forumulas around showing how it can be done with (DUPLICATE) and (SMALL) but I can't understand it at all.

Any help would be appreciated

Sheet 1 where the delivery price must be listed
[TABLE="width: 500"]
<tbody>[TR]
[TD]JOB ID[/TD]
[TD]Delivery Location[/TD]
[TD]Delivery Price[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]NSW[/TD]
[TD]$500[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]QLD[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]VIC[/TD]
[TD]$2500[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]SA[/TD]
[TD]$750[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2 where the delivery price comes from via our basic VLOOKUP

[TABLE="width: 500"]
<tbody>[TR]
[TD]JOB ID[/TD]
[TD]Delivery Location[/TD]
[TD]Delivery Price[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]NSW[/TD]
[TD]$500[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]QLD[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]VIC[/TD]
[TD]$2500[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]QLD[/TD]
[TD]$1500[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

You didn't mention column for Delivery Location so I have used column G. Adjust if required. This is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.

Excel Workbook
	C	D	E	F	G	H	I
1	JOB ID				Delivery Location		Delivery Price
2	1				NSW		$500
3	2				QLD		$1,500
4	3				VIC		$2,500
Sheet1