Ask Your Question

find, search or vlookup? [closed]

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 reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2020-09-03 23:49:01.646965

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

Question Tools

1 follower


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

Seen: 159 times

Last updated: Apr 16 '16