Index/Match

[Noz2k]

I have a list of numbers (in Sheet3 ColB) which I want to use to return the ID number on another sheet (Sheet 2 ColA) where the number in Sheet3 is equal to a number in column b or column c on sheet 2. However I can't seem to get it to run correctly.

at the moment my code is as follows

=INDEX(Sheet2!$A$2:$D$1458,MATCH(B2,Sheet2!$B$2:$B$1458,0))

I think the problem might be that there is more than 1 occurence of the number, (although the id would always be the same), if that's the case then what do I need to add so that it only looks up the first match?

 

[T. Valko]

I have a list of numbers (in Sheet3 ColB) which I want to use to return the ID number on another sheet (Sheet 2 ColA) where the number in Sheet3 is equal to a number in column b or column c on sheet 2. However I can't seem to get it to run correctly.

at the moment my code is as follows

=INDEX(Sheet2!$A$2:$D$1458,MATCH(B2,Sheet2!$B$2:$B$1458,0))

I think the problem might be that there is more than 1 occurence of the number, (although the id would always be the same), if that's the case then what do I need to add so that it only looks up the first match?

Can the number be in both columns B and C on the same row at the same time?

 

[Noz2k]

No, it can't


Just realised that it should be referring to column A at the end of my bit of code, but it still doesn't make a difference. All it returns is N/A

 

[T. Valko]

No, it can't


Just realised that it should be referring to column A at the end of my bit of code, but it still doesn't make a difference. All it returns is N/A

Ok, try this array formula**:

=INDEX(Sheet2!$A$2:$A$1458,MATCH(1,(Sheet2!$B$2:$B$1458=B2)+(Sheet2!$C$2:$C$1458=B2),0))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

 

[Noz2k]

From what I said this works perfectly. But I was mistaken when I said that the value couldn't be in both B & C, as seemingly it can. It works for all instances where the 2 numbers are different though. So thank you for that.

Just so I can learn from this, what does the 1 refer to in the Match function, the first instance?

 

[T. Valko]

From what I said this works perfectly. But I was mistaken when I said that the value couldn't be in both B & C, as seemingly it can. It works for all instances where the 2 numbers are different though. So thank you for that.

Just so I can learn from this, what does the 1 refer to in the Match function, the first instance?

Sort of, yes, indirectly.

Ok, that is based on your reply that the number won't be in both columns on the same row at the same time.

Consider this small example:

Book1
	A	B	C
1	Joe	0	0
2	Sam	0	X
3	Lisa	0	0
4	Sam	X	0
5	Allison	0	0
Sheet2

Array entered:

=INDEX(A1:A5,MATCH(1,(B1:B5="x")+(C1:C5="x"),0))

Result = Sam

We need to test if either column B or C contains X.

(B1:B5="x")+(C1:C5="x")

This is how we arrive at 1...

B1 = x = FALSE
B2 = x = FALSE
B3 = x = FALSE
B4 = x = TRUE
B5 = x = FALSE

C1 = x = FALSE
C2 = x = TRUE
C3 = x = FALSE
C4 = x = FALSE
C5 = x = FALSE

FALSE + FALSE = 0
FALSE + TRUE = 1
FALSE + FALSE = 0
TRUE + FALSE = 1
FALSE + FALSE = 0

So, we want to find the location of the first 1 in that array.

MATCH(1,{0;1;0;1;0},0)

The first 1 is found in position 2 so:

MATCH = 2

INDEX(A1:A5,2) = A2

= Sam

 

[Noz2k]

Ah, ok I understand. Thank you for explaining

 

[T. Valko]

Ah, ok I understand. Thank you for explaining

You're welcome. Thanks for the feedback!