Ask Your Question

find, search or vlookup?

asked 2016-04-15 15:55:51 +0100

caliray gravatar image

Conditions: spreadsheet with a list of id numbers in column A; adjacent cell in the same row of column B could contain a value or it could be empty. I need to find a specific ID of a range in column A and return true if the adjacent cell in column B contains a value or false if the cell in column B is empty.

If possible I would enter the test ID in C2 and have the true or false written into D2.

I don't know which is the proper function to use and how to set it up in a formula. I do understand somewhat how to use find, search and vlookup, but setting up a working formula is currently beyond my ability.

Thanks for any help.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2016-04-15 16:42:56 +0100

Lupp gravatar image

updated 2016-04-16 01:09:50 +0100

You may use in D2 a formula like =VLOOKUP($C$2;$A$2:$B$21;2;0)<>"" .
You will have to adapt the fix coded range $A$2:$B$21 to your needs or to parametrise it using OFFSET like in
=VLOOKUP($C$2;OFFSET(INDIRECT($A$1);0;0;$B$1;2);2;0)<>"" where $A$1 should contain (as a text) the starting address of your 2-column-data and $B$1 the number of rows to evaluate.

Edit with regard to the comment by @caliray:
Just in short: You may have a look into this demo I hastily sketched:

edit flag offensive delete link more


It seems pretty obvious that I did not state the conditions properly. I have a series of ID numbers in column A. I need to test those numbers against a control to see if any of the numbers in column A match the control. If the answer is yes, I need to return the row number where the match was found. I also need to test to see if an empty cell is found while testing column A to end the test. I can only write so much here and this is half of the problem. When solved, I'll go to the second part

caliray gravatar imagecaliray ( 2016-04-16 00:15:42 +0100 )edit

@calray:To clarify your question you can edit it. Best you append a section there to not cut the relation between my attempted answer and the original question.
Unfortunately I may not be able to get back to this for a few days.

Lupp gravatar imageLupp ( 2016-04-16 00:41:04 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2016-04-15 15:55:51 +0100

Seen: 114 times

Last updated: Apr 16 '16