CODEWITHSUNDEEP
excelexcel-formula
Beckie
Beckie

Reputation: 73

Excel - Find a value in an array and return the contents of the corresponding column

I am trying to find a value within an array and then return the value in a specific row in the corresponding column.

In the example below, I need to know which bay the Chevrolet is in: 

    Column A        Column C    Column D    Column E
    Chevrolet       Bay 1       Bay 2       Bay 3
                    Toyota      Ford        Saturn
                    Honda       Chevrolet   Jaguar
                    Ferrari     Subaru      Lexus
                    Mitsubishi  Hundai      BMW

I am looking for Chevrolet in the array C2:E5. Once it determines that the Chevrolet is in Column D, I need for it to return the value in D1. If it was in column E, I need it to return the value in E1.

Any help would be greatly appreciated. Thank you so much in advance.

Upvotes: 5

Views: 116476

Answers (4)

chris rogers
chris rogers

Reputation: 1

The SUMPRODUCT formula above is perfect for what I want.

E.g. to find the location - COLUMN and ROW - of a given value at A1 in a 2D Range A2:J44

use:  =SUMPRODUCT((A2:J44=A!)*(COLUMN(A2:J44)))  gives the Absolute* COL#
and : =SUMPRODUCT((A2:J44=A1)*(ROW(A2:J44))) gives the Absolute* ROW#

(not where it is in the range)

These can then be used in e.g. INDEX() or CELL() etc functions.

Upvotes: 0

L42
L42

Reputation: 19727

Try this Array Formula:

=INDEX($C$1:$E$5,1,SMALL(IF(NOT(ISERROR(SEARCH(A1,$C$1:$E$5))),COLUMN($A:$C),99^99),1))

or if you are sure that each column contains exactly what's being searched it can be written like this:

=INDEX($C$1:$E$5,1,SMALL(IF($C$1:$E$5=A1,COLUMN($A:$C),99^99),1))

Enter formula in any cell by pressing Ctrl+Shitf+Enter.

How does it work?
Our ultimate goal is to find the Column that contains the match:

  • First we did the search for the match using this formula: SEARCH(A1,$C$1:$E$5). It just checks if any of the entries matched A1. Actually, it can be simplified to $C$1:$E$5=A1 but I'm not sure if all entries in each column match exactly what's in A1.
  • That formula will produce an array of values when entered as array formula. Something like: {SEARCH(A1,C1), SEARCH(A1,D1), SEARCH(A1,E1);... SEARCH(A1,E5)}. The result will be array of number(s) and error (if non was found). But we don't want that, else we will be returning error everytime.
  • We then use IF(NOT(ISERROR(SEARCH(A1,$C$1:$E$5))),COLUMN($A:$C),99^99). This formula returns the Column Number if there is a match and a relatively huge number 99^99 otherwise. Result would be: {99^99, 99^99, 99^99, 2, ..., 99^99}.
  • And we are close to what we need since we already have an array of Column and huge number. We just use SMALL to return the smallest number which in my opinion is the lowest Column Number where a match is found. So SMALL(IF(NOT(ISERROR(SEARCH(A1,$C$1:$E$5))),COLUMN($A:$C),99^99),1) would return 2. Which is the column where Chevrolet is referenced at $C$1:$E$1.
  • Since we already have the column number we simply use INDEX Function which is: INDEX($C$1:$E$5,1,2).

Note: 99^99 can be any relatively large number. Not necessarily 99^99. Actual 16385(max column number in Excel 2007 and up + 1) can be used.

Result: enter image description here

Upvotes: 7

millionleaves
millionleaves

Reputation: 136

This formula will do it, assuming that the lookup value is in A1:

="Bay "&SUMPRODUCT((B2:D5=A1)*(COLUMN(B2:D5)))-1

You could easily adjust it to add more rows and columns.

The formula returns the column number that contains the lookup value and concatenates it with the word Bay to return the exact result you want.

The -1 at the end adjusts for the fact that the Bay 2 column is actually the third column in the worksheet, so you might need to adjust that offset as well.

The SUMPRODUCT function is much undervalued. More on it here:

http://fiveminutelessons.com/learn-microsoft-excel/multiply-two-columns-and-add-results-using-sumproduct

Upvotes: 0

Michael
Michael

Reputation: 2165

Another quick and dirty answer is to put a "dummy" row above the entire data set and then determine which placeholder column returned the correct result.

In B1, you can put the equation

=MATCH($A$2,B3:B6,0)

And then in C1, you can put

=MATCH($A$2,C3:C6,0)

And so on until you've covered all the rows and columns. Change the B3:B6 & C3:C6 to reflect the actual rows of data in the given column.

Now, the fun array formula which will actually return the bay. I have this array formula in cell A1 and it is looking from B1:D1, but you can move cell A1 anywhere you want and the B1:D1 range should be all the dummy columns you made above. Also, this is assuming that the bays you want are in row 2 (if they are in a different row, change R2C to R#C where # is the row number). In order to properly enter, enter the formula and then press CTRL+SHIFT+ENTER. 

=INDIRECT("R2C"&SUM(IF(ISERROR(B1:D1),FALSE,B1:D1))+1,FALSE)

If the formula is entered properly, it will show curly brackets { } around the equation when you single click on the cell.

Upvotes: 0

Related Questions