W.E.B
asked on
Lookup - Vlookup
Hello,
Can you please help me with a formula.
Column "D"
I need to lookup sheet 1 - Column "C" --- sheet 2 - Column" U"
If found, then if value in sheet 2 - Column AR = "FR" ----- Then Return value column "AE"
Column "E"
I need to lookup sheet 1 - Column "C" --- sheet 2 - Column" U"
If found, then if value in sheet 2 - Column AR = "FR" ----- Then Return value column "BA"
Column "F"
I need to lookup sheet 1 - Column "C" --- sheet 2 - Column" U"
If found, then if value in sheet 2 - Column AR = "FR" ----- Then Return value column "AT"
Column "G"
I need to lookup sheet 1 - Column "C" --- sheet 2 - Column" U"
If found, then if value in sheet 2 - Column AR = "FS" ----- Then Return value column "BA"
Column "H"
I need to lookup sheet 1 - Column "C" --- sheet 2 - Column" U"
If found, then if value in sheet 2 - Column AR = "FR" ----- Then Return value column "B"
Your help is greatly appreciated.
Sample Attached.
Sample.xlsx
Can you please help me with a formula.
Column "D"
I need to lookup sheet 1 - Column "C" --- sheet 2 - Column" U"
If found, then if value in sheet 2 - Column AR = "FR" ----- Then Return value column "AE"
Column "E"
I need to lookup sheet 1 - Column "C" --- sheet 2 - Column" U"
If found, then if value in sheet 2 - Column AR = "FR" ----- Then Return value column "BA"
Column "F"
I need to lookup sheet 1 - Column "C" --- sheet 2 - Column" U"
If found, then if value in sheet 2 - Column AR = "FR" ----- Then Return value column "AT"
Column "G"
I need to lookup sheet 1 - Column "C" --- sheet 2 - Column" U"
If found, then if value in sheet 2 - Column AR = "FS" ----- Then Return value column "BA"
Column "H"
I need to lookup sheet 1 - Column "C" --- sheet 2 - Column" U"
If found, then if value in sheet 2 - Column AR = "FR" ----- Then Return value column "B"
Your help is greatly appreciated.
Sample Attached.
Sample.xlsx
Sorry I forgot to mention that these are array formulas and have to be entered by pressing shift-ctrl-enter
ASKER
Hi Saqib,
I'm testing now,
I edited the question and added one column (H) to the sample.
Thank you very much.
I'm testing now,
I edited the question and added one column (H) to the sample.
Thank you very much.
Hi,
First create a Name Range for every required columns in Sheet2 with below formula:
Then in Sheet1 Column D below Array Formula confirmed with Ctrl+Shift+Enter:
Index-Match-Multiple-Criteria_Sampl.xlsx
First create a Name Range for every required columns in Sheet2 with below formula:
=OFFSET(Sheet2!$AR$2,0,0,COUNT(IF(Sheet2!$AR$2:$AR$1000="","",1)),1)
See Image below for Name Range:Then in Sheet1 Column D below Array Formula confirmed with Ctrl+Shift+Enter:
=IFERROR(INDEX(PK,MATCH(1,($C2=TR)*("FR"=ccc),0),1),"")
In Sheet1 Column E below Array Formula confirmed with Ctrl+Shift+Enter:=IFERROR(INDEX(CH,MATCH(1,($C2=TR)*("FR"=ccc),0),1),"")
In Sheet1 Column F below Array Formula confirmed with Ctrl+Shift+Enter:=IFERROR(INDEX(SVC,MATCH(1,($C2=TR)*("FR"=ccc),0),1),"")
In Sheet1 Column G below Array Formula confirmed with Ctrl+Shift+Enter:=IFERROR(INDEX(CH,MATCH(1,($C2=TR)*("FS"=ccc),0),1),"")
I am not sure about
Column "H"Please explain, In Sheet2 Column B is empty.
I need to lookup sheet 1 - Column "C" --- sheet 2 - Column" U"
If found, then if value in sheet 2 - Column AR = "FR" ----- Then Return value column "B"
Index-Match-Multiple-Criteria_Sampl.xlsx
Not tested
H1: =INDEX(Sheet2!$B$1:$B$615, MAX((C2=Sh eet2!$U$1: $U$615)*(S heet2!$AR$ 1:$AR$615= "FR")*ROW( Sheet2!$U$ 1:$U$615)) )
H1: =INDEX(Sheet2!$B$1:$B$615,
ASKER
Hi Saqib,
the formula is not bringing the correct amounts from sheet 2
Please see attached,
Hi Shums,
Testing your approcah now.
Sample.xlsx
the formula is not bringing the correct amounts from sheet 2
Please see attached,
Hi Shums,
Testing your approcah now.
Sample.xlsx
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you very very much guys.
As Always, Much appreciated.
Both worked.
As Always, Much appreciated.
Both worked.
Open in new window