How to return all the cells in a row if first column matches a value on another worksheet

microsoft excelsearchvlookupworksheet-function

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.

Best Answer

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.

Related Question