VLOOKUP with CONCATENATE

Hello All,

This is my first post on this site so bear with me

The problem I am having is that I want to look up something which has 4 different variables to it, in 4 different cell references i.e. Code, Site, Description, Dept, in order to find the answer i.e. the Unit Cost from the Lookup Spreadsheet. I can get a result by concatenating the 4 variables in the original formula, and creating a separate column in the Lookup spreadsheet.

However, is there a way of doing this on the original formula i.e. by concatenating the 4 columns from which I need to look up against to get the answer, or are there problems unless there is only one unique identifier.


Thanks

I think concatenating is the better solution. Or you can use array formula to match, but it is not advisable for large amount of data. I can give you some examples in the attached w.book

The way you are doing it would be best... but you can do it all in one formula too...

=INDEX('Sheet1'!$E$1:$E$100,MATCH(1,INDEX(('Sheet1'!$A$1:$A$100= A1)*('Sheet1'!$B$1:$B$100=B1)*('Sheet1'!$C$1:$C$100=C1)*('Sheet1'!$D$1:$D$100=D1),0),0))

Where Shee1, A1:E100 contain the lookup table (E1:E100 contains Unit Price)... and A1:D1 on active sheet contain the lookup values.

Thanks for those who responded the last time but I am still struggling to get this to work.
Here is my original e-mail, together with an attachement of what I am trying to look up to.

Hello All,

This is my first post on this site so bear with me

The problem I am having is that I want to look up something which has 4 different variables to it, in 4 different cell references i.e. Code, Site, Description, Dept, in order to find the answer i.e. the Unit Cost from the Lookup Spreadsheet. I can get a result by concatenating the 4 variables in the original formula, and creating a separate column in the Lookup spreadsheet.

However, is there a way of doing this on the original formula i.e. by concatenating the 4 columns from which I need to look up against to get the answer, or are there problems unless there is only one unique identifier.

Thanks