Lookup OR Index Match

Thanks in advance for any comments,
I need this help badly.

I have three columns

Company | Rank | Name

and I have data

What is the formula if I want the name of the person of specific rank (I will refer a cell as the input) of a specific company (I will refer a cell to get this data). Image and excel file attached.
Capture.PNG

Hello
Try this array formula, entered with Ctrl+Shift+Enter, in cell F3:

Formula:

Please Login or Register  to view this content.

If entered correctly, you will see curly brackets {} around the formula. Alternatively, you could use the DGET function but you would need another range of cells to link to the drop downs for the functions's criteria range.

DBY

it works, thanks. I am grateful.
a kind brief explain on how this works would be in immense help too.

thank you very much

The IF function returns:

{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;13;FALSE;FALSE;FALSE}

Where it tests for matches of Company and Rank in the Table1 'Company' and 'Rank' fields. As there is only one match it returns its Row number and FALSE for all the non-matching rows. The MIN function ignores FALSE and returns ( in the above example in red) the number 13. This is used by the INDEX function as its row number when returning a name from the 'Name' field.

Hope that makes some sense. Thank you for the Rep mark

DBY

Yes, it does, Thank you for your kindness. God bless you.

Or a non array formula:

=IFERROR(INDEX(Table1[Name],MATCH(1,INDEX((Table1[Company]=F1)*(Table1[Rank]=F2),0),0)),"")

This is great. Thank you Indi_Ra.

See more two formulas.

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Thanks All,
It's wonderful to see how many options we have to solve the same problem.

While thanking you all, among all these options I was amazed to see how nice the newdoverman's solution after I use these name ranges (I have converted the table in to a range and named Company column as CompanyList and Rank Column as RankList, Name Column as NameList, and the input cell of Company and Ranks to InputComany and InputRank.)

Then the formula became looks like this.

=Index(NameList,Match(InputCompany&InputRank,CompanyList&RankList,0)

wow, formula itself tells us what its doing....

Hi Dineth,

A version using SUMPRODUCT...


=OFFSET(C2,(SUMPRODUCT((Table1[Company]=F1)*(Table1[Rank]=F2),ROW(Table1[Name])))-2,0,1,1)

chullan88, thank you