Problem Lookup

[Kenshin]

Hell guys nice meeting you again, I need help with lookup stuff with difficult lay out, please see my attachment file

thank you

 

[bosco_yip]

D6,

=LOOKUP(2,1/(A1:A12="Tax number "&D3),B4:B15)

Regards

 

[Kenshin]

Thanks Bosco, but that not I want, what if column B is not blank but fill with data the formula will not works, Iam sorry for not clear

 

[bosco_yip]

Thanks Bosco, but that not I want, what if column B is not blank but fill with data the formula will not works, Iam sorry for not clear

Maybe,

In D6 :

=IF(COUNTA(OFFSET(B1,MATCH("Tax number "&D3,A1:A12,0)-1,0,3))>0,"",OFFSET(B1,MATCH("Tax number "&D3,A1:A12,0)+2,0))

Regards

 

[Kenshin]

Appreciate, but i have last request, what if the row is not the same amount? PLeas take a look at the file, thanks again

 

[bosco_yip]

Appreciate, but i have last request, what if the row is not the same amount? PLeas take a look at the file, thanks again

Try,

=IF(COUNTA(OFFSET(B1,MATCH("Tax number "&D3,A1:A12,0)-1,0,MATCH("Tax",INDEX(A1:A100,MATCH("Tax number "&D3,A1:A100,0)+1,0):A100,0)))>0,"",OFFSET(B1,MATCH("Tax number "&D3,A1:A12,0)+MATCH("Tax",INDEX(A1:A100,MATCH("Tax number "&D3,A1:A100,0)+1,0):A100,0)-1,0))

Regards

 

[Kenshin]

Thanks Bosco but the results is blank

 

[Kenshin]

But when I change this part
=IF(COUNTA(OFFSET(B1,MATCH("Tax number "&D3,A1:A12,0)-1,0,MATCH("Tax",INDEX(A1:A100,MATCH("Tax number "&D3,A1:A100,0)+1,0):A100,0)))>0

to

=IF(COUNTA(OFFSET(B1,MATCH("Tax number "&D3,A1:A12,0)-1,0,MATCH("Tax",INDEX(A1:A100,MATCH("Tax number "&D3,A1:A100,0)+1,0):A100,0)))=0

the results is okay

thanks you

 

[bosco_yip]

Change A12 to A100,

=IF(COUNTA(OFFSET(B1,MATCH("Tax number "&D3,A1:A100,0)-1,0,MATCH("Tax",INDEX(A1:A100,MATCH("Tax number "&D3,A1:A100,0)+1,0):A100,0)))=0

Regards

 

[Haseeb A]

Or...

=IFERROR(VLOOKUP("Tax",INDEX(A:A,MATCH(IF(D3="",NA(),"*"&D3),A:A,0)):B1000,2,0),"Not Found")

Change B1000 to last unused cell.

The below version will look for last unused cell dynamically.

=IFERROR(VLOOKUP("Tax",INDEX(A:A,MATCH(IF(D3="",NA(),"*"&D3),A:A,0)):INDEX(B:B,MATCH("zzz",A:A)),2,0),"Not Found")