Link to home
Create AccountLog in
Avatar of W.E.B
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
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image

From what I have understood
D2:    =INDEX(Sheet2!$AE$1:$AE$615,MAX((C2=Sheet2!$U$1:$U$615)*(Sheet2!$AR$1:$AR$615="FR")*ROW(Sheet2!$U$1:$U$615)))
E2:    =INDEX(Sheet2!$BA$1:$BA$615,MAX((C2=Sheet2!$U$1:$U$615)*(Sheet2!$AR$1:$AR$615="FR")*ROW(Sheet2!$U$1:$U$615)))
F2:    =INDEX(Sheet2!$AT$1:$AT$615,MAX((C2=Sheet2!$U$1:$U$615)*(Sheet2!$AR$1:$AR$615="FR")*ROW(Sheet2!$U$1:$U$615)))
G2:    =INDEX(Sheet2!$BA$1:$BA$615,MAX((C2=Sheet2!$U$1:$U$615)*(Sheet2!$AR$1:$AR$615="FS")*ROW(Sheet2!$U$1:$U$615)))

Open in new window

Sorry I forgot to mention that these are array formulas and have to be entered by pressing shift-ctrl-enter
Avatar of W.E.B
W.E.B

ASKER

Hi Saqib,
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:
=OFFSET(Sheet2!$AR$2,0,0,COUNT(IF(Sheet2!$AR$2:$AR$1000="","",1)),1)

Open in new window

See Image below for Name Range:
User generated imageThen in Sheet1 Column D below Array Formula confirmed with Ctrl+Shift+Enter:
=IFERROR(INDEX(PK,MATCH(1,($C2=TR)*("FR"=ccc),0),1),"")

Open in new window

In Sheet1 Column E below Array Formula confirmed with Ctrl+Shift+Enter:
=IFERROR(INDEX(CH,MATCH(1,($C2=TR)*("FR"=ccc),0),1),"")

Open in new window

In Sheet1 Column F below Array Formula confirmed with Ctrl+Shift+Enter:
=IFERROR(INDEX(SVC,MATCH(1,($C2=TR)*("FR"=ccc),0),1),"")

Open in new window

In Sheet1 Column G below Array Formula confirmed with Ctrl+Shift+Enter:
=IFERROR(INDEX(CH,MATCH(1,($C2=TR)*("FS"=ccc),0),1),"")

Open in new window

I am not sure about
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"
Please explain, In Sheet2 Column B is empty.
Index-Match-Multiple-Criteria_Sampl.xlsx
Not tested

H1:   =INDEX(Sheet2!$B$1:$B$615,MAX((C2=Sheet2!$U$1:$U$615)*(Sheet2!$AR$1:$AR$615="FR")*ROW(Sheet2!$U$1:$U$615)))
Avatar of W.E.B

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
SOLUTION
Avatar of Shums Faruk
Shums Faruk
Flag of India image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of W.E.B

ASKER

Thank you very very much guys.
As Always, Much appreciated.
Both worked.