IF two values are equal INDEX, to return name that MATCH on cell range

[score]

Hi there, may somone out there can help me with this formula in col. F48, IF(F48=($L$35),INDEX($I$29,MATCH($B$8:$B$11),0)), what i want the formula to do is if F48=L35, to return the name that is on cell I29 if that name match on the range of cell B8:B11, many thanks for any help.

[vlady]

try this out

Please Login or Register  to view this content.

[score]

Thanks vlady it does work ok but when i nested i can't get it to work, i have attached a file for a better reference so you can see what i have, What i need is if any value on the range of cells F47:F56 is equal to a value on any of the cells of the cells range of L17:L26, Q17:Q26, L33:42, T33:T42 return the name from either I13, Q13, I29, Q29 in the range of cells G47:G56 to who that value belongs to, the names data entry are in B8:B11 Eg. ihave a value of 9 in L35 and a value of 9 in T19 that are the same values that i have in F48 AND F49, in G48 should return Brent Dorrington (I29) and on G49 should return Jessee Mendez (Q13) or viseversa, because if function calculates in first order basis is returning the same name to both values in F48 AND F49, the same example with F52 and F53 have the same values in T34 and L19, so in G52 and G53 should return the names of Nathan Florence (Q29) and Billy Kemper (L19)

Hope this make sense, any help is greatly appreciated

[vlady]

maybe create a summary of the scores then from that summary get the names using index/small

Score Board 1 SCOREcopy.xlsx


look at sheet4

[score]

vlady, i really appreciate your help, this seems to work just fine but i can't figure it out on how to adjust that to sheet 1, is there may be another way around without having to create a summary, something like If(f48=t35),index(I29),MATCH(B8:B11) i don't know very much about excel but i think there have to be a way to do it with the data is already there. whatever im very graceful for your help, many thanks

[vlady]

i don't think that could be done.. you have 4 table that would be also changing, the array formula will return all (duplicates also)
you can create that summary in sheet 1 somewhere say column AD and AE where you can hide it starting at row 4 (AD4..AE4...)
the summary is just some kind of helper column where you would refer to the index/small

so the formula would become something like this

Please Login or Register  to view this content.

->array -ctrl+shift+enter

[score]

Attachment 198138vlady, thanks a lot for your help, i was able to manage the formula into my sheet and seem to work allright but one failure that i can't figure out, if you look at your example attached on sheet 4,is returning the wrong name on G4 it should return Bret Dorrington who have the value of 10 all other are returning the right name but that first one, is there is any way to fix this?

[vlady]

On my sample it returned Brent...can you check if there are curly braces in the formula in G4 it's array formula

[score]

On my sample it returned Brent...can you check if there are curly braces in the formula in G4 it's array formula

Very nice, that was the problem, i was not entering the formula correctly, thanks for taking of your time and help me solved, cheers!!

[vlady]

Your welcome ,cheers!

[score]

Hi vlady, i having a litter problem i can't figure out, please taka a look on sheet 1 cell G51, it does not want to return anything, just stay blank cell and i can't figure it out why, i have attached the file for you to check it out, but on your example on sheet 4 everything works perfect, i'm sorry to boder you again.

[vlady]

look at the $ signs should be properly put in the proper range/s

[score]

I look at the $ signs, they all seem to be on the proper range, any other suggestion?

[vlady]

that's odd, ...trying it later.

[score]

that's odd, ...trying it later.

Ok thanks, please le tme know if you can figure out what's wrong

[vlady]

here the file my bad..forgot to exclude blank spaces in small (if(range<>"",

Score Board vlady.xlsx

**fix column F i think I pasted values there, the formula is no longer there.

[score]

here the file my bad..forgot to exclude blank spaces in small (if(range<>"",

Attachment 198163

**fix column F i think I pasted values there, the formula is no longer there.

Now is working perfect, many many thanks, Cheers!!!

[vlady]

Cheers!!!!