complex vlookup to index match

thedeadzeds

Active Member
Joined
Aug 16, 2011
Messages
314
Guys, is there a way to change the v lookup part of the below formula to an index match so i dont have to keep changing the column index on the v lookup?

=IFERROR(IF($AN$3<>"",VLOOKUP(Summary!$AN$3,Tracker!$H:$NI,4,FALSE),IF(ISBLANK(INDEX(Tracker!$G$4:$NI$42,MATCH(Summary!$AN$2,Tracker!$G$4:$G$42,0),MATCH(Summary!J5,Tracker!$G$4:$NI$4,0))),"",INDEX(Tracker!$G$4:$NI$42,MATCH(Summary!$AN$2,Tracker!$G$4:$G$42,0),MATCH(Summary!J5,Tracker!$G$4:$NI$4,0)))), "")
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

thedeadzeds

Active Member
Joined
Aug 16, 2011
Messages
314
Hi

Tracker!$H:$NI is as below:

A1B1C1D1E1F1G1H1I1J1K1L1M1N1O1P1Q1
A2Total002211000
A3 SunMonTueWedThuFriSatSunMon
A4 Key NameTeam01/01/201702/01/201703/01/201704/01/201705/01/201706/01/201707/01/201708/01/201709/01/2017
A5 Bethan MasonBlue
A6 Full Day SickS Ceri O'SullivanBlue
A7 Half Day Sicks Fran MiguelBlue SS
A8 Full Day HolidayH Hannah CollinsBlue HHHH
A9 Half Day Holidayh Jodie ChiversBlue
A10 Maternity LeaveM Natasha BorowskiBlue
A11 Paternity LeaveP Nicole FoxwellBlue
A12 Unpaid Full DayU Sonia PingueBlue
A13 Unpaid Half Dayu Charlotte HanlonBlue
A14 Authorised Paid Full DayAF Debi CardBlue
A15 Authorised Paid Half DayAh Joanne SeymourBlue
A16 Training Full DayT Lewis WilliamsBlue
A17 Training Half Dayt Lorna YoungRed
A18 Louise BeamandRed

<colgroup><col span="2"><col><col span="3"><col><col><col span="9"></colgroup><tbody>
</tbody>
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
What do we have in AN3, AN2, and J5 of Summary? Also, care to clarify why VLOOKUP operates on H:NI while INDEX\MATCH on G:NI?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,655
Messages
5,597,384
Members
414,141
Latest member
Joey_T92

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top