Complicated Lookup or Index +Match function

[ahs004]

I have spreadsheet which has the details of the boilers installed in a property. in column A, I have the make of the boiler, Column B have the model and Column C have the size. for eg.

Worcester Greenstar 28i.

Now I need to look up this info from another sheet which has all the boiler details and pull off the manufactured date.

Is there any was of achieiving this info without Concanating the 3 columns to 1 and using vlookup?

Thank you.

[Fotis1991]

Using ARRAY INDEX & MATCH is a way..

[Shakey88]

if i understand u right, all you need to do is create a new column and combine the other columns (i.e. =A2 & " " & B2 & " " & C2)
this will give u a column u can use as a lookup_value...


[EDIT] Sorry just realized u said u didnt want to combine the columns... you could just combine them within the formulae =vlookup(A2 & " " & B2 & " " & C2,TABLE ARRAY,COL NUM,False) [/EDIT]

[ahs004]

Will you be able to post the formula please?

[Fotis1991]

=index(range,match(a2&b2&c2,range&range&range,0))

ARRAY formula.

-- Array(CSE) { }, formulae are confirmed with Control+Shift+Enter.
Not just Enter.

[ahs004]

Thanks for your help.

So far I did {=INDEX(range,MATCH(A2&B2&C2,Boilers!E:E&Boilers!F:F&Boilers!G:G,0))} but I need to pull off column J from boilers tab but I dont know how to do this. your help will be much appreciated.

[Fotis1991]

=INDEX(Boilers!j:j,MATCH(A2&B2&C2,Boilers!E:E&Boilers!F:F&Boilers!G:G,0))

and as an advice DON'T use whole columns reference. Formula will be too slow...

[ahs004]

you are amazing. Thanks very much for your help. I shall limit to the size of my spreadsheet.

[Fotis1991]

You are welcome and thanks for the feed back.

As that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED,as per Forum Rule #9. Thank you.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.