Match two values in two cells of a same row and return the value in a third cell

[Awais7376]

Hi There, I hope everyone fine there. I am at beginner level using Excel 2019 on MacBook Air. 

I have come across a situation:

I have created a data base using Hierarchies. if value is selected in cell A1 and another value is selected in Cell B1. I want excel to compare these two values and return the value in C1.

For Example:

A1 = A

B1 = B

than C1 should be = C

[#2]

@Awais7376 

Do you mean in C1 it shall be some formula which returns value based on values in A1 and B1 ? If so why "C" shall be returned, not "D" or "Z" ?

[#3]

@Awais7376 

The formula is always placed in the destination cell and sets the rules by which the value returned is to be calculated.  A general method might be through the use of a 2D lookup table.

but IF formulas would also offer a solution in some cases.

[#4 · Accepted solution]

thanks for reply. you have picked up correctly. I used C just for example. it may be any value resulting from combination of A and B according to the data provided to excel.
sometime, it may be A and C in both cells, so excel should look for the resultant value from with in the provided data and return the value in C1.

[#5]

@Awais7376 

Thank you, but I still don't catch what is the logic to generate " resulting from combination of A and B".

Let assume in A1 is 10 and in B1 is 11; or in A1 is "Joe" and in B1 is "Mary" - what shall be returned?

[#6]

that whole stats has been already provided to excel. for example: I have fed this whole data into the excel that if A1 is Joe and B1 is Mary than answer should be smith. if I change Mary to Shela in B1 than answer should be Jack. excel should find the answer with in the data which has already been provided. I hope I could make it clear............

[#7]

@Awais7376 

Is it possible to share sample file? It's not clear where provided data is located and in which form. In particular how Joe and Mary are connected to Smith in these records.

[#8]

hi Mr Baklan, really grateful for your quick and helpful responses. can you resolve another little problem. I have created few lists with names like "a, b, c, 101, 202". I have noticed that excel always put "_" after C and before the numerics. .e.g. "c_" "_101". why that happens and how to correct? Thanks

[#9]

For a start there are 17 billion potential names that are not available because they are cell references. Then another 17 billion to avoid the R1C1 cell references. You can't start a name with a number or use most symbols in a defined name. The underscores are just Excel getting around the rules.
A further note. If your multitude of names reference rows and columns of the data then
= (C_ _101)
will be the cell at the intersection of the two ranges.

[#10]

Thanks for a comprehensive answer. I am obliged.

[#11]

would you mind resolving another query of mine.
I have created a hierarchy. in cell A1, if I select "Alpha" then in cell B1, elements of list "Alpha" are displayed. now I want that in cell C1 (or any other cell), the same "Alpha of A1" should correspond to an other list (we may call it "Bravo"). is it possible???