Multiple VLOOKUPS in one formula?

mtetzlaf

Is it possible to create a formula that has multiple vlookups from different references?

If so, how? I've tried, unsuccessfully.

My goal is to create a formula that tracks the most recent number in a cell aug -10, sept-20, oct - "blank" so the cell will show 20. We have 4 sheets that track this info on a quarterly basis (q1 - aug, sept, oct), so i'd need this formula to take the most recently updated field across multiple sheet references. is this possible?

Paul Newcome

Are you able to provide some screenshots for reference?

mtetzlaf

Here you are! The first screenshot is the field in which the vlookup is in, the second shares the formula entered in that cell (adding in the below numbers), the third screenshot is the sheet in which the vlookup pulls from, the other sheets (three other) I want to pull the data from looks exactly the same. Hope this helps.

Andrée Starå

Hi @mtetzlaf

I hope you're well and safe!

Try something like this.

=SUM([HeadcountImpact]3:[HeadcountImpact]40) +" "+ VLOOKUP([RTA Code]1,{Q1 FY23 - Budget Analysis Range 2}, 9,false)

Did that work/help?

I hope that helps!

Be safe, and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

mtetzlaf

=IF(VLOOKUP([RTA Code]1, {Q4 FY23 - Budget Analysis Range 1}, 9, false) <> 0, VLOOKUP([RTA Code]1, {Q4 FY23 - Budget Analysis Range 1}, 9, false),

IF(VLOOKUP([RTA Code]1, {Q3 FY23 - Budget Analysis Range 1}, 9, false) <> 0, (VLOOKUP([RTA Code]1, {Q3 FY23 - Budget Analysis Range 1}, 9, false),

IF(VLOOKUP([RTA Code]1, {Q2 FY23 - Budget Analysis Range 1}, 9, false) <> 0, (VLOOKUP([RTA Code]1, {Q2 FY23 - Budget Analysis Range 1}, 9, false),

IF(VLOOKUP([RTA Code]1, {Q1 FY23 - Budget Analysis Range 2}, 9, false) <>0, (VLOOKUP([RTA Code]1, {Q1 FY23 - Budget Analysis Range 2}, 9, false), 0)))))

This is what I tried. My goal is to say if there is a value in the {Q1 FY23 Budget Analysis Range 2} then take that, but if there is a value in the {Q2 FY23 - Budget Analysis Range 1} then take that value in place of the other. I got an "#UNPARSEABLE" error with this formula.

Paul Newcome

It looks like there may be an issue with your parenthesis.

=IF(VLOOKUP([RTA Code]1, {Q4 FY23 - Budget Analysis Range 1}, 9, false) <> 0, VLOOKUP([RTA Code]1, {Q4 FY23 - Budget Analysis Range 1}, 9, false), IF(VLOOKUP([RTA Code]1, {Q3 FY23 - Budget Analysis Range 1}, 9, false) <> 0, VLOOKUP([RTA Code]1, {Q3 FY23 - Budget Analysis Range 1}, 9, false), IF(VLOOKUP([RTA Code]1, {Q2 FY23 - Budget Analysis Range 1}, 9, false) <> 0, VLOOKUP([RTA Code]1, {Q2 FY23 - Budget Analysis Range 1}, 9, false), IF(VLOOKUP([RTA Code]1, {Q1 FY23 - Budget Analysis Range 2}, 9, false) <>0, VLOOKUP([RTA Code]1, {Q1 FY23 - Budget Analysis Range 2}, 9, false), 0)))))

You could also use a MAX like so:

=MAX(VLOOKUP(.......), VLOOKUP(.......), VLOOKUP(.......), VLOOKUP(.......))

It will probably end up being much shorter and since each is only typed out once, it should also be easier to troubleshoot.

mtetzlaf

Actually, after double checking. this only pulls the MAX number out of the four vlookups.. I am looking for the most recent number entered.. if a value is in Q1, Q2, and Q3, but not Q4 then take Q3.

Paul Newcome

I am assuming you are meaning the MAX piece?

Did you retry the nested IF with the corrected parenthesis?

mtetzlaf

Yes, still got the unparseable error..

Paul Newcome

I think even I messed up the parenthesis and put one too many on the end. Try removing one as below?

=IF(VLOOKUP([RTA Code]1, {Q4 FY23 - Budget Analysis Range 1}, 9, false) <> 0, VLOOKUP([RTA Code]1, {Q4 FY23 - Budget Analysis Range 1}, 9, false), IF(VLOOKUP([RTA Code]1, {Q3 FY23 - Budget Analysis Range 1}, 9, false) <> 0, VLOOKUP([RTA Code]1, {Q3 FY23 - Budget Analysis Range 1}, 9, false), IF(VLOOKUP([RTA Code]1, {Q2 FY23 - Budget Analysis Range 1}, 9, false) <> 0, VLOOKUP([RTA Code]1, {Q2 FY23 - Budget Analysis Range 1}, 9, false), IF(VLOOKUP([RTA Code]1, {Q1 FY23 - Budget Analysis Range 2}, 9, false) <>0, VLOOKUP([RTA Code]1, {Q1 FY23 - Budget Analysis Range 2}, 9, false), 0))))