Forum Discussion
Need IF VLOOKUP to search more than just the first occurrence of unique ID
IF VLOOKUP is only searching the first occurrence of the unique ID rather than searching until it finds the unique ID that meets the IF criteria and returning that single result.
Context - I have a unique ID that may occur several times, but I only need to return info from the one unique ID that also says "Yes" in the Confirmed column. (There is only 1 unique ID that will also say "Yes") This is for recruiting purposes, so the "Yes" indicates the candidate has accepted their offer and "No" indicates a variety of statuses I don't need. The unique ID exists several times as there could be several no's on a job position before we reach a yes.
My formula =(IF(VLOOKUP($A2,'Consolidated'!$A:$AC,29,0)="Yes",VLOOKUP($A2,'Consolidated'!$A:$AB,2,0),"")
This is saying find the unique ID from A2 in the "Consolidated" tab and check column 29 to see if it is equal to "Yes" and then VLOOKUP and return the data from column 2 (to show the job title, or sub column 2 for any number to see other data about the job position and the candidate).
The formula works perfectly if there is only 1 occurrence of the unique ID. However, if there are >1 occurrence, then it will only return the data from the first occurrence of the unique ID instead of finding the unique ID that also meets the IF "Yes" criteria.
I also tried an INDEX MATCH with no luck accounting for the IF portion and searching multiple unique ID's to find the only row that has the unique ID that matches the IF "Yes" criteria. I've read about SMALL, but I don't understand how that could apply to this qualitative data when size is irrelevant.
Any help is greatly appreciated!
4 Replies
SOLVED!
Solution was to make a helper column CONCATENATE(ing) the unique ID with the yes/no column and then VLOOKUP from that instead.
With INDEX/MATCH, or better with XLOOKUP if available for you, you may avoid helper column working with concatenation of main columns directly in formula
=INDEX(lookup_column,MATCH(value & "yes",IDcolumn & yes/noColumns,0))Thanks for this suggestion.
How would you deal with this in the scenario of the attached file were you would need to use <> to search within a date range.
