help understand formulas

[j4ymf]

Hello

Prevously someone help me write these formulas
what they do is look up the first, second, third in a list im now trying to look up a forth and i can't get it to work any ideas what im doing wrong??

thank you

=IFERROR(INDEX(Download!D:D,AGGREGATE(15,6,ROW(Download!D:D)/(COUNTIF(B$8:B8,Download!D:D)=0)/(Download!F:F=B8),1)),"")

=IFERROR(INDEX(Download!D:D,AGGREGATE(15,6,ROW(Download!D:D)/(COUNTIF(B$8:C8,Download!D:D)=0)/(Download!F:F=B8),1)),"")

=IFERROR(INDEX(Download!D:D,AGGREGATE(15,6,ROW(Download!D:D)/(COUNTIF(B$8:G8,Download!D:D)=0)/(Download!F:F=B8),1)),"")

i cant see why the 4th is not working???

=IFERROR(INDEX(Download!D:D,AGGREGATE(15,6,ROW(Download!D:D)/(COUNTIF(B$8:K8,Download!D:D)=0)/(Download!F:F=B8),1)),"")

 

[maabadi]

Please upload Example file with XL2BB ADDIN (Preferable) or upload fit at free hosting site e.g. www.dropbox.com , GoogleDrive or OneDrive and insert link here.

 

[jasonb75]

Where did the original formula come from? I've looked through some of your old threads and found this similar example along with an alternative suggestion that would probably work better, but not the one that you are using.

Index Match

Hello I have this formula which someone on this forum has written for me but i need to change it to match the second option in the list. Thank you =IFNA(INDEX(Download!$J$2:$J$9999,MATCH(B42&"|"&A42,INDEX(Download!$F$2:$F$9999&"|"&Download!$Q$2:$Q$9999,0),0)),"")

www.mrexcel.com

 

[j4ymf]

Hello maabadi

thank you for getting back to me,
its a sheet i use for salarys so im struggling with out posting the data with it.
ill see what i can do.

thank you

 

[j4ymf]

Thank you Jason

it would of only come from this forum as this is the only one i use

thank you
ill try the other one you surgested

 

[j4ymf]

thanks jason,
I can get the original one to work, andy ideas to how i get the 4th answer?

jay

 

[jasonb75]

If your attempt in post 1 is not working then it is most likely an error in the data causing a mismatch.

I would suggest trying @Peter_SSs alternative suggestion, if it does the same then that would confirm it.

 

[j4ymf]

Jason

ive change it to this
=IFERROR(INDEX(Download!D:D,AGGREGATE(15,6,ROW(Download!J1:J9999)/((Download!F1:F9999=B8)*(Download!Q1:Q9999=A8)),2)),"")

how do i get the 2nd, 3rd, 4th answer then what bit do i change?

thank you

 

[jasonb75]

I was actually thinking the other formula in that thread, but with that one the 2 at the end denotes 2nd position.

One thing that is coming to mind, the COUNTIF part of the formula in post 1 is going to be looking at multiple rows and columns. If there are multiple results in the same row then that could be the cause. Without seeing an example everything that we suggest is going to be guesswork.

its a sheet i use for salarys so im struggling with out posting the data with it.

Save a copy, change the confidential information such as names, payroll numbers, pay amounts, etc to fictional but keep them similar (for example if payroll numbers are alpha numeric then your example should be the same, not numeric). Try to keep any duplicates in the same places as well.