1

I have an issue in excel where I cannot get it to return a specific value.

I have a table of data like this

Week     N1 N2  N3
w0  6   15  24
w1  5   8   9
w2  3   8   17
w3  20  23  31
w4  13  21  23
w5  6   12  15
w6  2   5   20
w7  10  20  21

the numbers in N1 N2 and N3 can change and are random.

What I need to be able to do is to lookup any number in the table and return the value in the weeks column.

so for example if I was looking for the number 20 it would return w6.

Ive tried various vlookups, Hlookups, Index and Match variations but just dont seem to be able to get this fairly simple thing to work.

Can anybody help me please, before I explode.

Improve this question
3
  • Why would it return w6? 20 appears several times in your table. How did you determine that it would choose w6 (and not w3 or w7)? Commented Feb 16, 2018 at 12:15
  • apologies yes, i want it to return the first instance that 20 appears, so in this example would be w3. Commented Feb 16, 2018 at 12:20
  • Please accept one of these answers as correct by placing a checkmark next to it. Commented Feb 16, 2018 at 12:33

2 Answers 2

Reset to default
3

Try this:

= INDEX($A$2:$A$9,MATCH(1,MMULT(($B$2:$D$9=20)+0,{1;1;1}),0))

EDIT A slightly better formula:

= INDEX($A$2:$A$9,MATCH(TRUE,MMULT(($B$2:$D$9=20)+0,{1;1;1})>0,0))

It returns the first Week where a match is found in the table.

Obviously just replace 20 above with whatever value you want to look up.

See example below.

enter image description here

EDIT

More generally, instead of hardcoding {1;1;1} into the formula, you can make this dynamic, e.g.

= INDEX($A$2:$A$9,MATCH(1,MMULT(($B$2:$D$9=20)+0,TRANSPOSE((COLUMN($B$1:$D$1)>0)+0)),0))

Also, if you want to search the table from left to right instead of top to bottom, do this:

= INDEX($A$2:$A$9,MATCH(1,MMULT(TRANSPOSE((ROW($A$2:$A$9)>0)+0),($B$2:$D$9=20)+0),0))

Note, both of these are now array formulas (must be entered with Ctrl+Shift+Enter instead of just Enter.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

Another method,

=index(A:A, iferror(match(G5, B:B, 0), iferror(match(G5, C:C, 0), match(G5, D:D, 0))))

enter image description here

Improve this answer

3 Comments

Technically, this returns the first match that is found from left to right, instead of top to bottom. Although, I'm not sure who's correct, since the OP doesn't specify whether to search top to bottom or left to right.
true but there are only 3 columns but 8 rows and my ctrl+v gets tired.
hi, the first answer provided does exactly what I need. thank you both for your replies

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.