nested formula limit

[Rhon Alvarez]

Hi Team,

I need help with this file (Reporting Requirements_v1), to extract data from another file (New ABS -06.01.2018).
I tried nesting the VLOOKUP however nesting limit won't allow me for there are 10 columns that I need to look the data from. Is there any formula, other than nested VLOOKUP, I can use to be able to extract necessary data.
I've attached both files for your reference, however, I opted to delete other sheets on New ABS file since it won't allow me to upload because of its size.

Thanks in advance.

 

[AliGW]

Which version of Excel are you using?

 

[Rhon Alvarez]

excel 2013. our office doesn't have any plans of upgrading yet to a higher version.

thanks.

 

[Peter Bartholomew]

The pattern of nested IFs you show can be replaced by CHOOSE (since IFS is not available to you). The first step is to use MATCH to find the index corresponding to the relevant term in $B1:$B10. That value is used as the first parameter of the CHOOSE in order to select the appropriate VLOOKUP.

In your particular case, the VLOOKUP range appears to be the same in all cases (give or take some additional columns on the right) so the entire formula can be reduced to a single VLOOKUP (or INDEX/MATCH). All that changes is the column number to return and that could be looked up knowing the result of the first MATCH. e.g.
= INDEX( {14;16;20;22;26;28;38}, MATCH($A$30,$B$1:$B$10,0) )

 

[NARAYANK991]

Hi ,

In case your problem has not yet been resolved , please upload the second workbook with the worksheet named ABS Template in it.

At present the second file which has been uploaded does not have this worksheet.

Narayan

 

[Rhon Alvarez]

Hi Narayan,

please find attached file as requested. I uploaded the same file but from another date since we have recently stop the use of some sheets included on the file. all i need was to extract data from the "IEX Dump ND 102 MU 2101" sheet which consists of different columns and rows and limit of vlookup is not allowing me to do such.

thanks in advance.

 

[NARAYANK991]

Hi ,

Can you please explain your requirement , in more detail , using Excel references ?

1. In which worksheet tab , in which cell does the formula need to be entered ?

2. Which other cells does it have to be copied to ?

3. What should the formula do , which is the input cells it should use to derive the output ?

4. Can you manually enter the required output in one or two cells so that what ever formula is suggested can be verified for correctness using these manully entered outputs ?

Narayan

 

[Rhon Alvarez]

Hi Narayan,

The formula needs to be in the "FTE_OCC" worksheet (d32 to I32 & d39 to I39) on the Reporting requirements_v1 file.

for "IBOR HR Protected..." the value should come from "New Abs.." file "IEX DUMP ND 102 MU 2101" sheet depending on the lookup value from "A30"

for illustration, on reporting requirements_v1:
if a30 = TMT prod
(rep.req_v1) d32 should be equal to (new abs.) AD125 ; e32 = ad126 ; f32 = ad127 ; g32 = ad127 and so on (transposition)
if a30 = TMT OJT
(rep.req_v1) d32 should be equal to (new abs.) Af125 ; e32 = af126 ; f32 = af127 ; g32 = af127 and so on (transposition)
if a30 = CORE prod
(rep.req_v1) d32 should be equal to (new abs.) Aj125 ; e32 = aj126 ; f32 = aj127 ; g32 = aj127 and so on (transposition)
and so on...

this should apply as well with IBOR PLANNED portion (d39 to i39) to be linked to rows 102 to 109 (same columns)

apologies for i couldn't provide inputs since the file is too large and I need to delete most of the sheets included. You may manually input data for checking purposes.

hope this could help in explaining further and thanks in advance.

 

[Rhon Alvarez]

just a screenshot..

 

[AliGW]

Upload the file, not a picture of it.

 

[NARAYANK991]

Hi ,

See if these formulae work.

I have defined 3 named ranges , which you will have to define in your work file.

Narayan