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"
C2 cell put formula =IFERROR(1+MATCH($A2,'tickets_info'!$A$2:$A$5000,0),0)
;
D2 cell put formula =IF($C2>0,INDIRECT("'tickets_info'!O"&$C2),"")
;
select C2 and D2 cells and copy formula down to line 500.
(optional) hide column C.
On sheet "tickets_info"
on O2 cell put formula =B2 & " " & C2 & " " & ... & N2
;
copy formula down to line 5000.
(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:
C2 cell put formula =IFERROR(1+MATCH($A2,'tickets_info'!$A$2:$A$5000,0),0)
;
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")),"")
;
(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)
select C2 to P2 cells interval and copy formula down to line 500 (or whatever).
(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.
Best Answer
I don't think there is any easy way to do this with existing Excel formulas. The problem is two-fold. First, AFAIK there is no Excel function that will tell you if a cell is part of a merged range. Second, the value shown in a merged ragne is actually only in the first cell of the merged range.
If you are willing to use VBA to create a custom function, this can be done fairly easily with a combination of the
Match
function and the fact that Range objects know if they are part of a merged range.