Using VBA How would you get multiple data when searching 3 other spreadsheets with a lookup?

[SNA400]

We currently have a list of 8 digit Codes where we need to search multiple spreadsheets to find out the complete 10 digit code from multiple rows of the same 8 digit code.

To give you an example of what I mean - Code 78071595 could return 7807159500, 7807159520, 7807159560, or 7807159590. this will always be from a single spreadsheet first and we always need the highest of these codes whatever it is.

Once we have this 10 digit code we need to search against another two spreadsheets to get other data back like if it has any duty payable, if there are any restrictions on sale etc.

Is this possible? I am sure it is, but for the life of me I cannot get the code to give me the info I need.

Can anyone help

Simon

 

[Herakles]

I don't know where you data range is but this works for me.

=MAX(IF(LEFT(($A$2:$A$26)*1,8)="78071595"&"",($A$2:$A$26)*1))

where the data is in A2:A26.

You can substitute "78071595" for a cell containing this value.