1

I am trying to do Exact match using Vlookup but it is not working with this formula.

IF(EXACT(A4,VLOOKUP(A4,'names'B:B,1,FALSE))=TRUE,VLOOKUP(A4,'names'B:B,2,FALSE),"False")

I have a tab which looks like this:

enter image description here

I have one more tab with in same spread sheet called names and looks like this:

enter image description here

I am trying to find the exact match of name from tab 1 with names and display True/False values in column D of the tab 1

The result that I am expecting is this:

enter image description here

Could any one please help me?

Forward Ed
  • 9,484
  • 3
  • 22
  • 52
TA Arjun
  • 25
  • 4

3 Answers3

1

=IFERROR(EXACT(INDEX($A$6:$A$8,MATCH(A3,$A$6:$A$8,0)),A3),FALSE)

enter image description here

Michal Rosa
  • 2,459
  • 2
  • 15
  • 28
0

I saw you beat me to it, but i thought would be nice if you can exctend the list by dragging it downards.

=IFERROR(EXACT(INDEX(Names!A$1:A20;MATCH(Sheet1!A18;Names!A$1:A20;0));A18);"")
Mikael
  • 57
  • 5
0

Use one of the following formulas in at the top of your True/False column and copy down:

=ISNUMBER(AGGREGATE(15,6,FIND(A1,Names!$B$3:$B$5),1))

Adjust your ranges to suit your data. Note the use of the Sheet name before the ! to indicate that a range of cells is on a different sheet. Other formulas will work as well as noted with other answer to your question.

enter image description here

enter image description here

Forward Ed
  • 9,484
  • 3
  • 22
  • 52
  • The above formulas are not working. I have Two sheets. I wanted to compare the names present in both the columns. For example: Sheet 1 Column A is names Sheet 2 : column B is names I want to check if a Column A names is present in Column B names list with exact match and if it is present then the formula should display True – TA Arjun Nov 28 '18 at 00:13
  • As said in the answer, you need to adjust your ranges (cell references) to suit your data. I have changed the photo and updated the formulas to match the new photos. – Forward Ed Nov 28 '18 at 00:29
  • It works:):) Thank you so much!!!! Is there a way to default the rule check column to blank instead of Falses until we have a name in column B of sheet 1 – TA Arjun Nov 28 '18 at 01:28
  • Put the whole thing in an IF statement. `IF(condition, what to do if true, what to do if false)` so what to do if false is "", What to do if TRUE can be TRUE or "MATCH". Condition is anything that returns TRUE or FALSE. Technically a math operation too with the understanding that 0 is FALSE and anything else is TRUE. – Forward Ed Nov 28 '18 at 01:32
  • The formula is failing in the below instance: sheet 1 REEMA TRUE arjun TRUE reema FALSE Test FALSE Names: REEMA arjun reema ARJUN – TA Arjun Nov 28 '18 at 01:56
  • It is failing when both the upper and lower case names are present in both the tabs – TA Arjun Nov 28 '18 at 02:01
  • Updated it with an alternate formula. Because vlookup is not a case sensitive search, It was finding the first occurrence of the name and not skipping over it once found. In this approach, FIND is case sensitive. Aggregate with formula 14/15 performs array like operations without being an array. – Forward Ed Nov 28 '18 at 02:56
  • The above formula is giving the same error again. Lets say I have Arjun and ARJUN in sheet 1 and in Names sheet, I have ARJUN and Arjun. the formula gives false for Arjun and True for ARJUN – TA Arjun Nov 28 '18 at 03:07