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

Microsoft OfficeMicrosoft ExcelSpreadsheets

Last Comment

W.E.B

8/22/2022 - Mon

Saqib Husain

4/7/2017

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

Saqib Husain

4/7/2017

Sorry I forgot to mention that these are array formulas and have to be entered by pressing shift-ctrl-enter

W.E.B

4/7/2017

ASKER

Hi Saqib,
I'm testing now,
I edited the question and added one column (H) to the sample.

Thank you very much.

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst

William Peck

Shums Faruk

4/7/2017

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:

Then 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

Saqib Husain

4/7/2017

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)))

W.E.B

4/7/2017

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

⚡ FREE TRIAL OFFER

Try out a week of full access for free.

Find out why thousands trust the EE community with their toughest problems.

SOLUTION