Roni wrote:
Guys,

I prefer to use the index and match function, although concatenate is
also working =).
Thanks for the advise.

Roni


Why? If you have a lot of retrieving to do or your spreadsheet is
infested with more array formulas, formulas with volatile functions,
etc., re-consider your preferences in terms of efficiency. Creating an
additional column by means of concatenation CLR suggested can be made
more robust with an improbable char like "#", "@", "," or a
non-printable CHAR(1)...

Let column B on Sheet2 house Serial # and C Asset Code, and D Location...

In A2 enter & copy down:

=B2&"#"&C2

Intermezzo. If you are on Excel 2003, convert the area A:D into a list
with Data|List|Create List. This list option will automatically copy the
concatenation formula down for every new record you might add.

[1] Invoke a VLOOKUP formula on Sheet1 with the match-type set to 0
(FALSE)...

=VLOOKUP(A2&"#"&B2,Sheet2!$A$2:$D$6,4,0)

[2] Sort the area A:E on Sheet2 in ascending order on Serial # then
Asset Code (With List, on the concatenation column) and invoke a fast
working LOOKUP formula or VLOOKUP formula with the match-type set to 1
(TRUE)...

=IF(LOOKUP(A2&"#"&B2,Sheet2!$A$2:$A$6)=A2&"#"&B2,L OOKUP(A2&"#"&B2,Sheet2!$A$2:$D$6),"")

=IF(VLOOKUP(A2&"#"&B2,Sheet2!$A$2:$A$6,1)=A2&"#"&B 2,VLOOKUP(A2&"#"&B2,Sheet2!$A$2:$D$6,4,1),"")