Excel Magic Trick #117: Reverse 2-Way Lookup Part 1

Really Hard formula!!! See the functions, IF, INDEX, MATCH, SMALL, ROW, ROWS, COLUMN and COLUMNS all in one array formula.

See how to do a Reverse 2-Way Lookup with a complex Array formula.

See how to populate a list of names for patients with schedule book entries using this complex Array formula.

Category:

Science & Technology

Tags:

• Excel
• IF
• INDEX
• MATCH
• SMALL
• ROW
• COLUMN
• function
• array
• formula
• Mike
• Gel
• Girvin
• Highline
• Community
• College

[chinchonchinchon]

Aladin?  Just rub the magic lamp and bum!

This is a fantastic formula.

[ExcelIsFun]

EMT 478 and 479 are much better than this one. You should watch those.

[chinchonchinchon]

@ExcelIsFun

Perhaps in the future the Excel people are going to create a formula or formulas to solve these kind of problems, instead of using small, if as in this one.

[ExcelIsFun]

That would be cool!

[gredangeo]

That formula is fucked. Damn you are good.

[ExcelIsFun]

Dear gredangeo,

I hope that means you found some use with the formula.

If you have duplicates, see this video for a formula that will work:

Excel Magic Trick #149: Reverse 2-Way Lookup No Duplicates

--excelisfun

[chinchonchinchon]

@ExcelIsFun

I meant functions, not formulas.

[tbaiyewu]

this guy is toooo much.... the formula is like a whole paragraph.

[Jakers2009]

this is nutz - fair play

[ExcelIsFun]

Dear soulsaver04 ,

The array comes from a few parts in the formula: The COLUMN and the SMALL(IF

Be sure to watch this video that shows a better formula for when you have duplicates:

Excel Magic Trick #149: Reverse 2-Way Lookup No Duplicates

--excelisfun