VBA code to Vlookup mulitple scenarios and combinations

[Wafee]

Can someone help me with a VBA code for the below scenario.

Sheet1 data.

1. Lookup using AB ID - Vlook up with Column B (Sheet2) and column E (Sheet1) for the status, if the result is NA then,
2. Lookup up between Column H (Sheet2) and Column F (sheet1) and in that if there is a version for that in column J (sheet2) then it also neeeds to be matced with column G (Sheet2) to obtain Status. if the result is again NA then leave it as blank.

to re-iterate AB ID's needs to looked up, if NA then FG ID's needs to be looked up and if FG ID's has a version then it also needs to be combined.

thank you in advance.

​

Column E - AB ID​	Column F - FG ID​	Column G - FG ID Version​	Column H - Status​
AB325​	FG321​	2​	Initiated​
AB246​	​	​	Development​
AB389​	FG441​	6​	Testing​
​	FG321​	5​	Deployment​
AB457​	FG643​	11​	Development​
AB238​	FG762​	1​	Initiated​

Sheet 2 data (Output - Column K)

Column B - AB ID	Column H - FG ID	Column J - FG ID Version	Column K - Status
AB238	FG762	1	Initiated
	FG321	5	Deployment
AB246			Development
AB457			Development

 

[onlyadrafter]

Hello,

Why do you need VBA?

I'm sure there must a simpler formula,

In K1 enter

=IF(ISERROR(MATCH(B1,Sheet1!$E$2:$E$21,0)),INDEX(Sheet1!H:H,MATCH(H1&J1,Sheet1!$F$1:$F$21&Sheet1!$G$1:$G$21,0),0),VLOOKUP(B1,Sheet1!$E$2:$H$21,4,0))

and press CTRL + SHIFT + ENTER, then copy down.

Change row 21 to how ever many rows necessary.