SOLVED

Create an Excel formula that has conditional & lookup arguments in 2 workbooks

Copper Contributor

PC; Windows 10 O/S; Excel in Microsoft 365.

Create a conditional (or logical - not certain of the correct term) Excel formula that argues,

[1] if a value in cell ($C$3) in "Workbook A"  equals a value in a cells in Column Range (A1:A16) in "Workbook B," AND,

[2] If a value in another cell (A11), in Column Range (A9:A27) in "Workbook A" equals a value in a cell (AK4) in Row Range (A4:CF4) in "Workbook B",

Returns TRUE with the value in cell (AK11) in "Workbook B",

Returns FALSE with the value "".

NOTE: There are "Blank" cells in the Row Range (A4:CF4). But the columns are not blank below those cells.

2 Replies
best response confirmed by Abraham M. Payton Jr. (Copper Contributor)
Solution

@Abraham M. Payton Jr. Took your question literally and created a mock-up of the situation that I believe you described. Attached two workbooks including a rather ugly formula in A1 of Workbook A. 

=IFERROR(IF(ISNUMBER(MATCH(C3,'[Workbook B.xlsx]Sheet1'!$A$1:$A$16,0)),INDEX('[Workbook B.xlsx]Sheet1'!$A$11:$CF$11,1,MATCH(A11,'[Workbook B.xlsx]Sheet1'!$A$4:$CF$4,0)),""),"")

Open both workbooks at the same time and you should be able to test it. 

Very likely, though, that there are better ways to do this, but for that you would have to share the real workbooks A and B. Replace any confidential information with fake data, if you should decide to do so.

@Riny_van_Eekelen Thank you so very much. This works just like I wanted it to. I apologize if I confused the problem by not providing the information in a way that made it easy for you to help me. Again, thank you!

1 best response

Accepted Solutions
best response confirmed by Abraham M. Payton Jr. (Copper Contributor)
Solution

@Abraham M. Payton Jr. Took your question literally and created a mock-up of the situation that I believe you described. Attached two workbooks including a rather ugly formula in A1 of Workbook A. 

=IFERROR(IF(ISNUMBER(MATCH(C3,'[Workbook B.xlsx]Sheet1'!$A$1:$A$16,0)),INDEX('[Workbook B.xlsx]Sheet1'!$A$11:$CF$11,1,MATCH(A11,'[Workbook B.xlsx]Sheet1'!$A$4:$CF$4,0)),""),"")

Open both workbooks at the same time and you should be able to test it. 

Very likely, though, that there are better ways to do this, but for that you would have to share the real workbooks A and B. Replace any confidential information with fake data, if you should decide to do so.

View solution in original post