CODEWITHSUNDEEP
arraysexcelsearchreturn
GeMa
GeMa

Reputation: 149

excel search value in a table and return another value

I need to search in a table a value and reurn the value of the last column of the corresponding row (I can transverse rows and columns if it is going to simplify the functions). For example, I have the following table and when I give as the search value 75, I want to have the value 204 (value of the last column of the same row). The last column can be also moved to be the first one, in case it helps the function. I tried with Index and Match and Lookup but these functions need one column array to search into.

41  42  43  44  61  62  63  64  81  82  83  84  101 102 103 104 201
45  46  47  48  65  66  67  68  85  86  87  88  105 106 107 108 202
49  50  51  52  69  70  71  72  89  90  91  92  109 110 111 112 203
53  54  55  56  73  74  75  76  93  94  95  96  113 114 115 116 204
57  58  59  60  77  78  79  80  97  98  99  100 117 118 119 120 205
121 122 123 124 141 142 143 144 161 162 163 164 181 182 183 184 216
125 126 127 128 145 146 147 148 165 166 167 168 185 186 187 188 217
129 130 131 132 149 150 151 152 169 170 171 172 189 190 191 192 218
133 134 135 136 153 154 155 156 173 174 175 176 193 194 195 196 219

Upvotes: 0

Views: 204

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60164

=INDEX($Q$1:$Q$9,AGGREGATE(14,6,1/($A$1:$P$9=R12)*ROW($A$1:$P$9),1))

If first row of table not Row 1, see note below for adjustment

enter image description here

How it works:

  • ($A$1:$P$9=R12) returns a 2D array of FALSE with a TRUE at the point of equality.
  • 1/... returns an array of #DIV/0! and a 1 for the TRUE
  • Multiply by ROW(...) to get the row number
  • Use that row number to INDEX into the table.
    • Note that if the table does not start in Row 1, you need to subtract the first row of the table (-1) from the calculated row number.
    • If you use a Table or a named range, this can be made dynamic.

Edit: simpler formula

If you will only ever need to have a single result (eg all entries in the table will be unique, you can use:

=INDEX($Q$1:$Q$9,MAX(($A$1:$P$9=R12)*ROW($A$1:$P$9)))

Upvotes: 1

Solar Mike
Solar Mike

Reputation: 8365

I would use index() with match() like so:

enter image description here

So, used iferror() with match() to check each column and return the position if found, if not then blank.

Then index() with max() to get the position found and return the value from column Q.

Formula in cell A15 dragged to P15 is

IFERROR(MATCH($S$2,A4:A12,0),"")

Formula in cell T2 is

INDEX(Q4:Q12,MAX(A15:P15))

Cell S2 is the value you are looking for.

Upvotes: 0

Related Questions