lookup formula need (2 look up and one criteria)

[ryan4646]

Hi all,

i need one urgent solution for the below matter.

In sheet one, i need to fill up unit information (column k) , from second sheet column "I" exactly
matching with "A" and "E" column. In sheet one unit (column "k") A and E column has to match with the same information at second sheet.

i am helped by this forum many times, hope this time also i will get on solution asap. cheers.

[benishiryo]

hi ryan. what happens when there is a duplicate value? this array formula would pick the first one:
=INDEX(GR!$I$2:$I$1083,MATCH(A2&E2,GR!$A$2:$A$1083&GR!$B$2:$B$1083,0))

...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 your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

this would take the average & is much faster:
=AVERAGEIFS(GR!I:I,GR!A:A,A2,GR!B:B,E2)

[ryan4646]

HI BEISHIRYO

actually i cannot take the first one because by order to order and by month the unit price gets change. so i have to take the unit price "k" based on data in the second sheet exactly matching with my second sheet (column A and B)

[RobertMika]

Try this:
=INDEX(GR!$I$2:$I$1083,MATCH(MASTER!A2&MASTER!E2&MASTER!J2,GR!$A$2:$A$1083&GR!$B$2:$B$1083&TEXT(GR!$F$2:$F$1083,"MMM"),0))
Confirm Control+Shift+Enter
This will talke into account month in column J

[:) Sixthsense :)]

May be slight addition in Beni’s formula

In K2 Cell - Array Formula - Requires CTRL+SHIFT+ENTER

=IFERROR(INDEX(GR!$I$2:$I$1083,MATCH(A2&E2&TRIM(J2),GR!$A$2:$A$1083&GR!$B$2:$B$1083&TEXT(GR!$F$2:$F$1083,"MMM"),0)),"-")

Array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

Drag it down…

Wait until the calculation gets completed

[ryan4646]

Hi sixth sense,

here is the attached file after pasting formula as it is now showing any data. actually you can segregate J column from the formula.there is nothing with it. Just need exact matching K column of first sheet from second sheet "I" column and the look ups are "A" and "E" from first sheet.

real sorry for confusing if i made any

[RobertMika]

IFERROR does not wotk in Excel 03(your file is saved as xls)
Use this
=IF(ISERROR(INDEX(GR!$I$2:$I$1083,MATCH(A2&E2&TRIM(J2),GR!$A$2:$A$1083&GR!$B$2:$B$1083&TEXT(GR!$F$2:$F$1083,"MMM"),0))),"-",INDEX(GR!$I$2:$I$1083,MATCH(A2&E2&TRIM(J2),GR!$A$2:$A$1083&GR!$B$2:$B$1083&TEXT(GR!$F$2:$F$1083,"MMM"),0)))
or switch to 07 version

[ryan4646]

hi Mika,


I hope some of cells didnt take the duplicate value. Actually not duplicate, those are the same items that bought in month after month.

attaching file of formula outcome as it is showing lot of "-" sign.

very sorry for the inconvenience.

[RobertMika]

COATS TOTAL BUY 2013.xls
You are having issues with the date format (UK/US) where months is as a first in the date.
I have added formula in column N sheet GR.
Please see attached

[ryan4646]

Hi robertmika,

very sorry for the inconvenience. dont know why the formula is showing some numbers, where it should show unit price only. And actually i want it on the first sheet "k" column.
attaching screen shots.

[RobertMika]

The formula on the screenshot is showing the date in correct format.
The formula in sheet Master column L should read
=MATCH(A2&E2&TRIM(J2),GR!$A$2:$A$1083&GR!$B$2:$B$1083&TEXT(GR!$N$2:$N$1083,"MMM"),0)
and is just showing matching rows in sheet GR.
COATS TOTAL BUY 2013.xls
Hope that helps