Tjyylli

I have 2 worksheet tabs in an Excel workbook, tickets and tickets info. I have values in Column A of tickets (from A2:A500). I want to search for each value from A2 to A500 of the ticket sheet looking up in column A of tickets_info. If there is a match, return all of the cells for that row (ticket_info has information for each ticket from Column A to Column N).

I'm using the following formula:

=VLOOKUP(A2,tickets_info!A$2:N$5000,2,FALSE)

writing it in the ticket sheet, column C (column A contains the ticket Number and column B contains user name).

I have used A2 to N5000, which is the range of the data from the ticket_info sheet, which contains many columns of information concerning each ticket. The ticket numbers are in Column A from A2 to A5000.

I am encountering "Invalid Reference Error" when I commit the formula.

Ok, I think I got what you're trying to do and I think VLOOKUP could not be the way to go for this, using MATCH instead. Let's begin...

1ST APPROACH "all-values-in-a-single-cell":

• 
  

  On sheet "tickets"

  
  
  
  
  

  1. C2 cell put formula =IFERROR(1+MATCH($A2,'tickets_info'!$A$2:$A$5000,0),0);

  
  

  2. D2 cell put formula =IF($C2>0,INDIRECT("'tickets_info'!O"&$C2),"");

  
  

  3. select C2 and D2 cells and copy formula down to line 500.

  
  

  4. (optional) hide column C.

  
  
  
  


• 
  

  On sheet "tickets_info"

  
  
  
  
  

  1. on O2 cell put formula =B2 & " " & C2 & " " & ... & N2;

  
  

  2. copy formula down to line 5000.

  
  

  3. (optional) hide column O.

  
  
  
  

EDIT:
What if, for some reason, you want/need to leave "tickets_info" sheet untouched?

You can make it using this partly modified version of the formula on sheet "tickets" D2 cell: (what was huge formula revisited after night sleep restoration)

=IF($C2>0,INDIRECT("'tickets_info'!B" & $C2)

  & " " & INDIRECT("'tickets_info'!c" & $C2)

  ...

  & " " & INDIRECT("'tickets_info'!N" & $C2),"")

or (even longer)

=IF($C2>0,INDIRECT(ADDRESS($C2,2,,,"tickets_info"))

  & " " & INDIRECT(ADDRESS($C2,3,,,"tickets_info"))

  ...

  & " " & INDIRECT(ADDRESS($C2,14,,,"tickets_info")),"")

then copy down. This way you won't need column O formula on "tickets_info" anymore (and thus there's no need to modify it in any way).

Notice I didn't write all those "repetitive" code blocks. Obviously, for the formula to work properly, those ... must be replaced adding the remaining necessary coding blocks with column reference incremented for each other column you need to show.

A FINAL NOTE FOR THIS EDIT: the final formula will be long but could have been really huge if we wanted to do without the intermediate value formula on C2 and using a unique combined C2/D2 formula on that cell (but that's highly awkward in my opinion, so I'm definitely not going to show you how!)

2ND APPROACH "a-single-cell-per-value" - UPDATED:

If you want to display values from "tickets_info" sheet B2 to N2 on separate columns on "tickets" sheet then here is the variant:

1. C2 cell put formula =IFERROR(1+MATCH($A2,'tickets_info'!$A$2:$A$5000,0),0);



2. 
   

   D2 cell put formula (old way) =IF($C2>0,INDIRECT("'tickets_info'!B"&$C2),"");

   
   
   

   (updated alternative) =IF($C2>0,INDIRECT(ADDRESS($C2,COLUMN()-1,,,"tickets_info")),"") ;

   
   


3. 
   

   (if doing the old way) copy/paste formula on D2 to the E2 to P2 cells interval BUT make sure to change ("increment") that B between ! and & to the necessary C, D, ..., N on the other columns where you copied the formula;

   
   
   

   (if using updated alternative - MY PREFERRED)
   What's important to understand here is that using COLUMN() in the formula let's you need not to remember to "step-by-step" change the !B"anymore as you copy the formula on the adjacent columns - isn't this freedom? ;-) Then you can simply copy/paste the formula on D2:P2 cells interval without having to change anything and thus (can't tell about performances but) this would be for sure the best choice if you want to avoid the risk of errors that may come by forgetting to increment references after copying formulas ...or even if you simply are lazy at writing/modifying repetitive code-blocks (...just like I am! ;-D)

   
   



4. select C2 to P2 cells interval and copy formula down to line 500 (or whatever).




5. (optional) hide column C.

That's it.

A FINAL NOTE ON THIS APPROACH (to dissect the way down): I often prefer MATCH to VLOOKUP but, in this case, I went straightway with it because, at first, I thought you needed a way to retrieve the whole corresponding row values on "tickets_info" with a single lookup; even the intermediate values calculated on column C are basically there for the same reason. Once you need individual values you could even get rid of them combining C/D columns formulas like follows:

=IF(ISERROR(1+MATCH($A2,'tickets_info'!$A$2:$A$5000,0)),"",INDIRECT(ADDRESS(1+MATCH($A2,'tickets_info'!$A$2:$A$5000,0),COLUMN()-1,,,"tickets_info")))

and simply copy/paste it to the C2:O500 cells interval.

Finally, in this case, as fixer1234 also suggested, VLOOKUP becomes a possible alternative in such a (even little shorter to write) way:

=IF(ISERROR(VLOOKUP($A2,'tickets_info'!$A$2:$N$5000,COLUMN()-1,FALSE)),"",VLOOKUP($A2,'tickets_info'!$A$2:$N$5000,COLUMN()-1,FALSE))

P.S. What to do in case you face formatting issues after you copy/past formula?

• In case you went with second approach, just format the cell using the format you need (i.e. date/time for date and time values); you can use regular cell formatting for that, or define a custom formatting if you wish or, more simply (and highly recommended!) copy the original column(s) on sheet "tickets_info" from where values are retrieved and paste/special-only-formatting to the respective "ticket" sheet copy-destination column(s).




• In case you went with first one, you may need to convert some retrieved numeric value to formatted text using TEXT function.
  One thing to be aware of in this case is that using TEXT function you will lose the ability to use the original value to make operations such as, for example, date or time calculations, tests to see if date meets certain conditions, etc.

So the advice would be to go with the first approach only when/if:

a) you are simply dealing with retrieving text values (numbers or anything already stored on text-formatted columns), as it wouldn't make any difference;

or b) a simple concatenated-copy of values is enough and you are not planning FOR SURE you'll need them individually to make any operations.

...otherwise do not think of it and follow always the second approach eyes closed, it certainly won't hurt.

Now, getting to the conclusion, at (long) last, I add some link suggestions by fixer1234 about the latter discussed matters:

How to create custom number formatting (with complete set of codes).

Explanation of TEXT function usage to convert a supplied numeric value into text.

I had the same issue and used the following:

• In the Tickets sheet, Select C2 to O2


• 
  

  Without clicking anywhere, start typing:

  
  
  

  
  

  =VLOOKUP(A2, tickets_info!$A$2:$N$5000, {2,3,4,5,6,7,8,9,10,11,12,13,14,15}, FALSE)

  
  

  
  


• Press Ctrl+Shift+Enter at the same time


• Autofill down

Guess I'm missing something about this, but it looks to me as if you have values on the "second" (output) page in column A and want to find their matching data for columns B through N. They would be a direct match: if a row's A column value exists in the "first" (source) sheet, then its F column value goes in the output sheet row's column F, etc. Some values in the A column on the output sheet will NOT match anything on the source sheet and so that row should be blank.

If so, just do the VLOOKUP()'s in column B checking for column A's value in the source material's column A and returning the value it finds in column 2 of the source material (column B, in short).

Note that in the output page's column 2, you want data from column 2 of the source sheet. And so on to the right. Here then is the twist:

for the column to return argument (3rd argument), do NOT use a hand-typed numeral, but rather " Column() " and each column's cell will get the correct source column's value.

Nothing more clever than that. Lots of VLOOKUP()'s though, but that's probably OK. My plain vanilla setup is OK with that for 5,000 rows, so my guess would be yours is too.

But... if it doesn't match anything? Well, use an IF() test for the column B VLOOKUP() and return "" if column A in that row doesn't match anything in the source column A.

And now, the magic. Who wants to rely on "probably OK" or be held down to 5,000-ish rows, or even double that, then choking? No one.

Excel evaluates formulas from left-to-right AS MUCH AS it possibly can and STOPS evaluating when it has the result. If it had an IF() test first and its result = TRUE, it would skip anything else and go to the result for TRUE (and stop if that's a "constant" like "8" or "tree" or evaluate what's there if it is itself a function or two. So if you add an IF() test to all the cells in columns C through N like the following:

=IF( $B2 = "", "", VLOOKUP( $A2, tickets_info!$A$2:$N$5000, COLUMN(), FALSE))

then ALL those columns rightward from column C will swiftly evaluate to "" if there was no match for the column A value and never do the VLOOKUP()'s! (TRUE in that case is "" which is a constant and needs no further evaluation so Excel stops and displays "".) So no more "probably OK", no choking any time soon. If there are only 943 rows of values to check on the output page, and only 634 of them match on the source page, only 634 * 12 = 7,608 extra VLOOKUP()'s are done, not 5,000 * 12 = 60,000.

Also, if your source data would never change (once on the source sheet, it might be sorted up or down, but would NEVER be edited), then you could always periodically copy a tranch of the output cells and Paste|Special|Values to do away with their formulas which would further reduce the "load."