Find information by row (3 columns of data) -- If A and B match our inputs, output C

[hdiaz36]

Have a spreadsheet with 3 columns of data. The data in column A has many identical entries, the data in column B, there are also a few cells with identical values. However, in no two rows is there a combination of identical info in both column A and B. What I would like to do is come up with a formula whereby I can specify what to look for in column A, what to look for in column B, and once the unique pairing is found, the output should be what is in column C.

Something like If A="" and B="" what is C

After doing some reading I was trying to fiddle around with a combination of index and match functions, but didn't come close to getting through this as yet. Does anyone have some good ideas? The biggest problem I have encountered with my meek attempts with this is that one a match is found in column A (For example A21) then rather than looking at other areas in column A, the formula will only look at the corresponding value in column B and if it is not what we're looking for it will return an error message.

I came to these conclusions by using formula editing, and I am convinced what I have been working with is totally wrong - which is why I haven't bothered posting it here.

Does anyone have some good suggestions? I feel this is a fairly easy task for an advanced excel user. Actually, I feel like many years ago this would have been a breeze for me as well, but I'm long out of practice.

PS: I'm using Excel 2003

 

[jarjarbingie]

Have a spreadsheet with 3 columns of data. The data in column A has many identical entries, the data in column B, there are also a few cells with identical values. However, in no two rows is there a combination of identical info in both column A and B. What I would like to do is come up with a formula whereby I can specify what to look for in column A, what to look for in column B, and once the unique pairing is found, the output should be what is in column C.

Something like If A="" and B="" what is C

After doing some reading I was trying to fiddle around with a combination of index and match functions, but didn't come close to getting through this as yet. Does anyone have some good ideas? The biggest problem I have encountered with my meek attempts with this is that one a match is found in column A (For example A21) then rather than looking at other areas in column A, the formula will only look at the corresponding value in column B and if it is not what we're looking for it will return an error message.

I came to these conclusions by using formula editing, and I am convinced what I have been working with is totally wrong - which is why I haven't bothered posting it here.

Does anyone have some good suggestions? I feel this is a fairly easy task for an advanced excel user. Actually, I feel like many years ago this would have been a breeze for me as well, but I'm long out of practice.

PS: I'm using Excel 2003

=INDEX($C$2:$C$7,MATCH($F2&$G2,$A$2:$A$7&$B$2:$B$7,0)) press CTRL+SHIFT+ENTER

F2 = criteria for column A
G2 = criteria for column B

 

[hdiaz36]

=INDEX($C$2:$C$7,MATCH($F2&$G2,$A$2:$A$7&$B$2:$B$7,0)) press CTRL+SHIFT+ENTER

F2 = criteria for column A
G2 = criteria for column B

Thank you for the response.

What I have is this

[TABLE="width: 44"]
<tbody>[TR]
[TD="class: xl63, width: 44"]=INDEX(StudentNumberPT,MATCH(Sheet1!$A$1&Sheet1!$A$3,ClassColumn&StudentColumn,0))

StudentNumberPT = column C
ClassColumn = column A
StudentColumn = column B

These three columns about are all on sheet 2

A1 and A3 (from sheet one) are the criteria for column A and B of sheet 2, respectively.

I am getting a #NUM ! error.[/TD]
[/TR]
</tbody>[/TABLE]


EDIT: Again, I'm using Excel 2003 as that might always be relevant.

 

[jarjarbingie]

Thank you for the response.

What I have is this

[TABLE="width: 44"]
<tbody>[TR]
[TD="class: xl63, width: 44"]=INDEX(StudentNumberPT,MATCH(Sheet1!$A$1&Sheet1!$A$3,ClassColumn&StudentColumn,0))

StudentNumberPT = column C
ClassColumn = column A
StudentColumn = column B

These three columns about are all on sheet 2

A1 and A3 (from sheet one) are the criteria for column A and B of sheet 2, respectively.

I am getting a [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=NUM]#NUM [/URL] ! error.[/TD]
[/TR]
</tbody>[/TABLE]


EDIT: Again, I'm using Excel 2003 as that might always be relevant.

hmm, not sure why you are getting that error unless you have #NUM ! error in column C?

 

[hdiaz36]

It's probably because those names I defined refer to the entire column (to account for future additions to the data), rather than to a specific limited range. I just tried it using exact ranges for columns A and B and now the formula works.

Thanks so much for your assistance.