CODEWITHSUNDEEP
excelvba
user3719861
user3719861

Reputation: 1

Searching for an excel entry with 2 conditions to be met

I have a dataset on excel with more than 40k entries, and there are 2 columns.

I want to find if there exists an entry for example with 1 in the first column and 1687 in the second column, I tried using this following IF function:

=IF(COUNTIF(B2:B178,"1687"),"Yes","No)

where A1:A178 have 1 in the first column.

This function works perfectly alright, however in terms of selecting the range for Countif, it requires much work as sometimes the range i have is more than 3 to 4k. This is way too time consuming as I have to repeat this search for many pairs of numbers.

I then tried by adding a helper column, where the 1st entry would be "=A2&B2" My plan was to search this entire helper column for "11687" instead, however this fails to identify entries where it has 1 in the first column, 1687 in the second, and 11 in the first column, and 687 in the second column.

Ideally if the "find" function (Ctrl+F) could find 2 columns for different values it will be exactly what I need.

Could anyone please advise me on how to do this search?

Thank you very much!

Upvotes: 0

Views: 44

Answers (2)

Tarik
Tarik

Reputation: 11209

Change your range into a table and use built in Excel filtering capabilities. See http://office.microsoft.com/en-001/excel-help/filter-data-in-a-range-or-table-HP010073941.aspx#BMfilter_text

Upvotes: 1

bamblack
bamblack

Reputation: 3779

Try this formula and fill it all the way down, should be more efficient than COUNTIF:

=IF(AND($A1="1",$B1="1687"),TRUE,FALSE)

Then you have a column of TRUE/FALSE that you can use to do what you want.

Upvotes: 2

Related Questions