Index / match

[leopardhawk]

Trying to create a formula that will search column B for the number 69 and return the corresponding value from the same row in column G.

Having a brain cramp.

Thanks for any assistance!

 

[Fluff]

Re: need help with INDEX / MATCH

How about
=INDEX($G$2:$G$20,MATCH(69,$B$2:$B$20,0))

 

[leopardhawk]

Re: need help with INDEX / MATCH

In this particular case, cell B19 has the 69 and the value to be returned from G19 is 122712 but your suggestion is returning 0. By the way, I should also politely request that the formula always return a zero (0) if the number 69 cannot be found in column B.

 

[kweaver]

Re: need help with INDEX / MATCH

=iferror(index($g$2:$g$21,match(69,b2:b21,0)),0)

 

[Fluff]

Re: need help with INDEX / MATCH

If it's returning 0 then there is nothing in col G for the first instance of 69 in col B
Are you sure that B19 is the first instance of 69?
Also do you have any merged cells?

 

[leopardhawk]

Re: need help with INDEX / MATCH

Appreciate your help.

Yes, B19 is the first and only instance of 69 and the number 122712 is in G19. There are merged cells above the array that I am using on the worksheet which just happens to be B16:B100 and G16:G100. Will this affect the results?

 

[Fluff]

Re: need help with INDEX / MATCH

As long as none of the cells in B16:B100 or G16:G100 are merged then it shouldn't be a problem, but if F9:G19 were merged then it would return 0.

Excel 2013/2016
	B	C	D	E	F	G
13				122712
14
15
16	66					122709
17	67					122710
18	68					122711
19	69					122712
20	70					122713
21	71					122714
22	72					122715
23	73					122716
24	74					122717
25	75					122718
26	76					122719
27	77					122720
Sheet3

Cell Formulas
Range	Formula
E13	=INDEX($G$16:$G$100,MATCH(69,$B$16:$B$100,0))

 

[leopardhawk]

Re: need help with INDEX / MATCH

Fluff, I'm embarrassed. Your formula works perfectly! Hard for me to admit but I had column G wrong, it's column F... must be too early in the day for me or maybe I didn't get enough sleep last night. Sorry about that! Appreciate your help very much!

 

[Fluff]

Re: need help with INDEX / MATCH

Not a problem, we've all been there.

If you use this it will return a 0 if no match id found
=IFERROR(INDEX($F$16:$F$100,MATCH(69,$B$16:$B$100,0)),0)

 

[leopardhawk]

Re: need help with INDEX / MATCH

Thank you, that also works as intended. However this has brought a new problem to light. While 90%+ of the users will be younger than 69, if the user is 70 or older, the number 69 will not be in column B at all and while it's not giving an error, I am wondering if it's possible to have the formula return the amount in F16 if the user is past age 69?