VLOOKUP (or HLOOKUP, or LOOKUP) please help/suggest

[boo]

Ok, I have a spreadsheet with a grid of numbers (17 columns, and 200 rows, if that matters) that correspond to some text (also in spreadsheet) to right of numbers. The numbers should already be unique (just all jumbled up)

1. 1 60 76 91 131 162 177 text-fieldUNO text-field-DOS
2. 2 61 77 92 132 163 178 text-unique text-again-unique
3. 3 62 78 93 133 164 4566 more-text etc-etc

QUESTION: I need to get output as follows:

• 60 text-fieldUNO text-field-DOS
• 61 text-unique text-again-unique
• 62 more-text etc-etc
• [then continuing on consecutively to the last number:]
• 4566 more-text etc-etc

I’ve read all the vlookup, hlookup, lookup, array, etc pages, but my brain isn’t getting how to easily get from the giant grid (because vlookup only pulls from the first column and it cannot search a comma-separated-list) to an ordered list that spits out the text fields that appear to the right on same-row.

The output DOES NOT need to be sorted (I can easily paste to another sheet and sort by column, but it has to dump each unique number from 60 to 4566 along with the corresponding text that’s on the same line as the number. Just looking for some fast help pretty-please?

[robleyd]

To clarify, the values shown in your first example are all in separate columns? Do you then have 15 columns of numbers followed by two columns of text? And the output needs to be three columns?

[boo]

You are exactly correct. There are 17 columns with unique numbers followed by 2 columns with text (that same text is associated with all 17 of the numbers appearing to the left). Some cells are blank (like not every row is filled with 17 numbers, a few only have 2). Huge thanks for taking a look.

Other potential tools I’m looking at are INDEX and MATCH.

[robleyd]

Does the output have as many rows as the source? And is the search value always in the same column? For instance, your example above suggests all the ‘lookup’ numbers are in column 2 and that the lookups range from 60 to 4566

[boo]

yes, output has same number of rows as source. CORRECTION: my example is misleading, the numbers to be searched-for appear NOT JUST in column 2 but ALSO in columns 3-17 as well. … as far as the “search value” I may be a bit confused or reading too much in, but I want to “search for” all the numbers from 60-4566 and dump the corresponding 2 text fields that appear next to the table.

[boo]

I’ll be here until I solve this. Continuing to look at possible solutions. Thanks a ton for any suggestions. (still here)

[boo]

OK, here’s the kludge I used for anyone that finds this later:

1. First I input the numbers I wanted to solve for (60 - end). Put these in a column below out of the way.
2. I used MATCH to find out which of the various 17 columns contained a match for each consecutive number I had between 60 to end. MATCH outputs the number of the row it appeared on (which is significant because that row # is the same as each item number if you see the example above). Formula used here: =IFERROR(MATCH($Q485,B$1:B$59,0),0). The IFERROR was to return a zero rather than an error (important later). This outputs a little grid with zeros mostly but importantly ONE FIELD contains the “match”.
3. Once I knew which numbers mapped to which row, I used a VLOOKUP to barf out the text fields. =VLOOKUP(SUM($B61:$P61),$A$1:$S$59,18,0)

…ok likely that won’t do much to help anyone. If anyone else posts a better way I’ll be happy to accept the answer, but I think I got it done.