How to add to "IF" formula?

[jgomez]

What I’d like to do is depending on what is chosen from the drop down box I would like for it to bring it up on cell B7-D7.

I’ve managed to bring over John but what if Joe was chosen? How would I add to the same formula where if Joe is chosen to bring his information instead?

In this case John = 2, Joe =3, Mary =4 and Jane =5…

Oh and any recommendations on what reading will help me understand on how to add more criteria to a formula??

[NBVC]

Try:

=IFERROR(INDEX($J$12:$L$15,$O$1-1,COLUMNS($A$1:A$1)),"")

copied across.

[jgomez]

Thanks, Why did you put "COLUMNS($A$1:A$1)" ???

[NBVC]

That indicates the column number from the table to get the info from...

COLUMNS($A$1:A$1) is equal to 1

as you copy it to next column you get COLUMNS($A$1:B$1) which equal 2 and then to next column COLUMNS($A$1:C$1) equals 3

you can replace with hard-coded 1, 2 and 3 if desired.

[jgomez]

Thanks again... ran into a problem the list it self will consit of about 2,000 names, in some cases they will be identical but the formula only brings up one of the names not both...

question why did you subtract "$O$1-1"... i'm sorry just trying to understand. I get everything else now.

[NBVC]

The $O$1-1 was just subtracting 1 from the linked cell value that appeared in O2 as a result of your selection... since you have a blank as the first selection the formula had to incorporate the offset of 1. I hadn't noticed that you had a different range of names to point your drop down list to in M3:M7... and I guess I assumed that the list in I12:K15 was the main list and therefore the names were listed in the same order...

So instead it would have been better to use:

=IFERROR(INDEX($J$12:$L$15,MATCH(INDEX($M$3:$M$7,$O$1),$J$12:$J$15,0),COLUMNS($A$1:A$1)),"")

or

=IFERROR(INDEX(J$12:J$15,MATCH(INDEX($M$3:$M$7,$O$1),$J$12:$J$15,0)),"")

...but now with your new problem of duplicate matches, instead try:

=IFERROR(INDEX(J$12:J$2000,SMALL(IF($J$12:$J$2000=INDEX($M$3:$M$7,$O$1),ROW($J$12:$J$2000)-ROW($J$12)+1),ROWS($A$1:$A1))),"")

confirmed with CTRL+SHIFT+ENTER not just ENTER and copied down as far as you need (without going too far as the formula becomes less efficient) and copied across the 3 columns.

[jgomez]

How did you get the 3rd formula to work without having any of the cells from K12:l16?

[NBVC]

Notice that the indexed range is not made absolute I.e. No $ signs before the Js so when you copy across it turns to Ks then to Ls. This is another technique that allows you to avoid using the Columns function to determine column number

[jgomez]

dude, you're so smart!