Lookup values

paul29berks

Active Member
Joined
Mar 15, 2004
Messages
293
I wondered if someone could help. I have forgotten the formula to look up values.

Basically I have 1 column that contains Codes (Shown as column 2 below)and I need to verify that these codes are valid against another column (Column 1 below) by doing a simply lookup. I wish to return the value True or false dependant on whether the code exists or not.

Can someone else.

Thanks

Paul

For example

Column 1 (valid codes) Column 2 Codes to check)

12345 11
1234 5
123 124
12
1
11
124
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
Something like this:

Adjust ranges and copy down Column 3

=IF(ISNA(MATCH(B1,$A$1:$A$100,0)),"FALSE","TRUE")
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
paul29berks said:
I wondered if someone could help. I have forgotten the formula to look up values.

Basically I have 1 column that contains Codes (Shown as column 2 below)and I need to verify that these codes are valid against another column (Column 1 below) by doing a simply lookup. I wish to return the value True or false dependant on whether the code exists or not.

Can someone else.

Thanks

Paul

For example

Column 1 (valid codes) Column 2 Codes to check)

12345 11
1234 5
123 124
12
1
11
124

Assuming that the valid list is in A2:A100 and the codes to check in B2:B4...

=ISNUMBER(MATCH(B2,$A$2:$A$100,0)+0

1 means valid, 0 invalid.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,811
Messages
5,574,448
Members
412,595
Latest member
slim313
Top