Forum Discussion

JMcElwain1's avatar
JMcElwain1
Copper Contributor
Oct 25, 2019

formula help - vlookups doing weird stuff

I know the subject is pretty vague, but I wasn't sure how else to phrase it.

 

OK, this is going to be a bit longwinded, please ask for clarification if its too messy.

 

I have a handful of workbooks. The project is to have one main workbook that pulls data from the others. Pretty simple right? The sheets are linked and auto update, that's no problem. The issue arises when I use a vlookup to reference another tab. If the criteria is not found, the cell remains blank due to an IFERROR statement to leave it blank upon error, pretty straightforward.

 

What I want the sheet to do, however, is when the criteria is not found, rather than leave the cell blank, I want the formula to move on to the next cell. One solution I'm finding is to just sort the referenced data. But I don't want to have to do that every time the workbook is opened.

 

 

My goal is to have those blank cells replaced with the data found upon matching criteria - make sense?

 

I want the formula to return this:

 

 

Thanks for any advice, I'm open to all ideas 🙂

 

 

Jeremy

 

 

excel
Formulas and Functions

6 Replies

  • Subodh_Tiwari_sktneer's avatar
    Subodh_Tiwari_sktneer
    Silver Contributor
    Oct 25, 2019

    JMcElwain1 

    Which formula you are currently using? Is your formula fetching the values from another Worksheet in the same Workbook? If yes, could you please share a sample workbook (not an image) after removing any sensitive info?

    • JMcElwain1's avatar
      JMcElwain1
      Copper Contributor
      Oct 25, 2019

      Subodh_Tiwari_sktneer 

       

      Here's what I've got:

       

      Yes, the lookup is fetching values from a tab that holds all of the data. I'm trying to break the data into two tabs based on the vlookup criteria.

       

      I've attached the file. Thank you!

       

       

       

      • Subodh_Tiwari_sktneer's avatar
        Subodh_Tiwari_sktneer
        Silver Contributor
        Oct 25, 2019

        JMcElwain1 

        Try this Array Formula which requires confirmation with Ctrl+Shift+Enter instead of Enter alone.

         

        On Booked Sheet,

        In A2

         

        =IFERROR(INDEX('All Inquiries'!B$2:B$4,SMALL(IF('All Inquiries'!$A$2:$A$4="B",ROW('All Inquiries'!$A$2:$A$4)-ROW('All Inquiries'!$A$2)+1),ROWS(A$2:A2))),"")

         

        Confirm with Ctrl+Shift+Enter and copy it across and down until you get blank cells.

         

        Exactly same formula you need on Cold Sheet, only the criteria "B" will be replaced with "C" in the formula.

         

        Please find the attached for more details.

         

        If you have access to dynamic array formulas, you may try the Filter function like below...

        In A2

        =FILTER('All Inquiries'!B2:C4,'All Inquiries'!A2:A4="B","No record found!")