Multiple IF statements

Copper Contributor

I am looking at finding a formula for:

=IF(B25=Sheet1!A2 than B26=Sheet1!B2 and B27=Sheet1!C2 .... and so on BUT

=IF(B25=Sheet1!A3 than B26=Sheet1!B3 and B27=Sheet1!C3

9 Replies

It seems that you need a formula like this in B26, copied down rows:
=INDEX(CHOOSE(1+(B$25=Sheet1!A$3),
Sheet1!B$2:J$2,Sheet1!B$3:J$3),
ROW()-25)

@Twifoo 

I have tried your formula and it works for the first drop down, but when I drop down to the second name which is on Sheet1!A$2 it doesnt pick up the line he is on. Am I missing something?

Please attach your sample file so I can see what's causing the error.

@Twifoo Please see attached Thanks.

What's the address of the formula you're referring to?

@Twifoo the drop down in Sheet2 B25, so when you choose PR it pre fills Sheet2 B26:B29 from the info in Sheet1 B2:F2

And Im hoping that you can get it to do the same thing when you choose MB from the Sheet2 B25 drop down to pick up the info in Sheet1 B3:F3 to show in the Sheet2 B26:B29 fields

 

@Luke_williams 

In the attached file, I modified Sheet2!A26 from "Manager Position" to "Role" so that it will coincide with Sheet1!C1. Thereafter, a simple VLOOKUP will return your desired results in Sheet2!B26:B29. Thus, the formula in Sheet2!B26, copied down to Sheet2!B29, is: 

=VLOOKUP(B$25,Sheet1!A$2:G$31,
MATCH(A26,Sheet1!A$1:G$1,0),0)

@Twifoo Great! thank you

It's a pleasure to know that!