typical type of lookup match function

[paradise2sr]

Hi,

This is typical type of lookup which data is needed to extracted from 'Lookup' sheet into 'Required'sheet.

In 'Required' sheet following is to be looked up to match the data.

In 1st C2 & C3
In 2nd C5 & C6
In 3rd C8 & C12 & C13

Above data must be matched with B column and 5th row of 'Lookup' sheet to pull data in 'Required' sheet in D column in respective highlighted cells.

With Best Rgds

[Glenn Kennedy]

You could use an array formula like this:

=INDEX(lookup!$M$7:$O$9,MATCH('Required '!C2,lookup!$B$7:$B$9,0),MATCH('Required '!C3,"Wastage : "&lookup!$M$5:$O$5,0))

in D3. Note that there were two leading spaces in C3 which had to be deleted. Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

[hemesh]

you could also use
=SUMPRODUCT((lookup!$B$7:$B$9='Required '!C2)*(MID(C3,FIND(":",C3)+2,100)=lookup!$M$5:$O$5)*lookup!$M$7:$O$9)

change the references accordingly

[hemesh]

you can use below enter in D2 and drag down

=IFERROR(SUMPRODUCT((lookup!$B$7:$B$9=INDEX(C:C,MATCH(MAX($B$2:B2),B:B,0)))*(MID(C2,FIND(":",C2)+2,100)=lookup!$M$5:$O$5)*lookup!$M$7:$O$9),"")

[paradise2sr]

It worked well.Thanx all of u.

[Glenn Kennedy]

Great! I'm glad to have helped! If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[hemesh]

That's Nice to see that solutions provided here worked for you.

As a token of appreciation you can add reputation to all members who helped you.