CODEWITHSUNDEEP
excelexcel-formula
Fiínek Cahů
Fiínek Cahů

Reputation: 67

Excel formulas create

Hello I have a problem with excel formulas =IFERROR(OR(IF(C2=INDEX(data!A2:A70126;MATCH(C3;data!B2:B70126;0));TRUE;FALSE);IF(C2=INDEX(data!A2:A70126;MATCH(C3;data!B2:B70126;0));TRUE;FALSE));FALSE)

I have a column A where is contain data for cell C2 and column B where is data for cell C3. But I have a problem when I search a match. for example:

Column A (cell C2) ----- Column B (cell C3)

551-021 --------------------- 1055102000001

551-021 --------------------- 1055102000002

551-021 --------------------- 1055102000003

551-021 --------------------- 1055102000004

010.551-025 --------------- 1055102000000

010.551-025 --------------- 1055102000001

010.551-025 --------------- 1055102000002

010.551-025 --------------- 1055102000003

The problem is that for a different value in column "A" and cell "C2" with the same code from column "B", and the cells "C3"

When i search match 010.551-025 and 1055102000002 show me FALSE but for 551-021 and 1055102000002 show me TRUE. Do you know how can I repair it?

Thank you

Upvotes: 0

Views: 69

Answers (1)

Aditya Pansare
Aditya Pansare

Reputation: 1132

Your index function INDEX(data!A2:A70126,MATCH(C3,data!B2:B70126,0) returns 551-021 when C3 = 010.551-025. This why when you search match 010.551-025 and 1055102000002 shows FALSE and for 551-021 and 1055102000002 shows TRUE.

Use below mentioned Array Formula to get the desired result.

{=IF(OR(C2&"-"&C3=A1:A9&"-"&B1:B9),"True","False")}

Here Enter your data in Column A and Column B. Put values you want to search at C2 (value to be searched in Column A) and at C3 (value to be searched in Column B).

To write Array Formula press CTRL+SHIFT+ENTER.

Upvotes: 1

Related Questions