r/excel•4 yr. ago

VLOOKUP with a twist

solved

Hey,

I have a sort of a hierarchic structure and I want to VLOOKUP it's values but output on just one column. I can't figure out if that's even possible?

Below is a table of what my sheet looks like and what I'm trying to achieve, I have columns from 1 to 3 and randomly they might have a value or not, depending on the hierarchic structure I'm going to make. If I just use VLOOKUP I get 3 columns where one gets the information and the rest is #EMPTY and even if use a formula to get rid of empty cells, they will still be in the 2nd or 3rd new columns. But for readability's sake I'd want them to be on the same column (A4), regardless of the position of the cell that I'm comparing to the other sheet.

A1	A2	A3	A4
Aa			VLOOKUP(A1:A3;"other worksheet"!A:B;2;0)
	Bb
		Cc
	Dd
Ee

Thanks for any input.

Posted Feb 24, 2022, 8:36 AM UTC

Sort by:

spinfuzer227•4 yr. ago

If the other two cells are ALWAYS empty just concat them

replace A1:A3 with CONCAT(A1:A3)

366dfOP•4 yr. ago

Solution Verified

Clippy_Office_AsstMOD•4 yr. ago

You have awarded 1 point to spinfuzer

^(I am a bot - please contact the mods with any questions. | ) ^(Keep me alive)

366dfOP•4 yr. ago

They are, it's always one cell, 2 empty cells. I couldn't get Concat to work within the Vlookup. Then if I just concat into a column and vlookup that I get the same #EMPTY. I guess it's because the result is a formula and it's being compared to a string?

I can get by with combining the cells, then later turning the formulae to numbers and doing the Vlookup, but I wonder if there's a way to include the Concat in the formula so that there's one step less?

spinfuzer227•4 yr. ago

Maybe you have spaces in your blank columns

Try TRIM(CONCAT(A1:A3))

if you try CONCAT(A1:A3) = A1 (your non blank cell) do you get true? If not you probably have a space that needs to be TRIMMED.

Another possibility is if your lookup value is numbers you need to add a VALUE() to turn it back into a value.

366dfOP•4 yr. ago

No spaces in the blank cells and trimming doesn't seem to do anything.

CONCAT(A1:A3) = A1 gives false.

Probably less headache to just concat into a column, change the formulae in that column to whatever the format I'm comparing it to and vlookup that. Thanks.

AutoModeratorMOD•4 yr. ago

u/366df - Your post was submitted successfully.

Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

Decronym•4 yr. ago

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

|Fewer Letters|More Letters| |-------|---------|---| |CONCAT|2019+: Combines the text from multiple ranges and/or strings, but it doesn't provide the delimiter or IgnoreEmpty arguments.| |TRIM|Removes spaces from text| |VALUE|Converts a text argument to a number| |VLOOKUP|Looks in the first column of an array and moves across the row to return the value of a cell|

^(Beep-boop, I am a helper bot. Please do not verify me as a solution.)
^(3 acronyms in this thread; )^(the most compressed thread commented on today)^( has 13 acronyms.)
^([Thread #12956 for this sub, first seen 24th Feb 2022, 10:46]) ^[FAQ] ^([Full list]) ^[Contact] ^([Source code])