PDA

View Full Version : Solved: Lookup Values Across Tabs and Set Flag



RKramkowski
04-15-2010, 12:31 PM
I have a spreadsheet where I have a master list and three additional tabs (see attached "Starting Point.xlsx" in the .ZIP file) The master is the aggregate of all unique values of the tabs. I need to do a lookup that looks for the value in column A and if it finds a corresponding match in tabs 2, 3, or 4 that it sets a flag that the entry was found in that list. I have a column on tab 1 for each subsequent tab. The lookup values are all text and the number of entries could vary for each tab. The flag doesn't have to be "yes" or "no", it could be 1 and 0. It should end up looking like "Ending Point.xlsx" in the .ZIP file.

Thanks in advance for your help.

Bob

austenr
04-15-2010, 01:38 PM
=if(isna(vlookup(A2,'Condition 1',$A$2:$A$14,1,false)),"No","Yes")

put this in B2 of the master worksheet and copy down. Use the same formula in C2 and D2 copying down changing the worksheet name and absolute reference cell ranges.

RKramkowski
04-16-2010, 01:03 PM
Thanks,

It initially didn't work but you gave me enough to go on to make it work. the final is:

=IF(ISNA(VLOOKUP($A2,'Condition 1'!$A$2:$A$11,1,FALSE)),"No","Yes")

I had to reference the range on the Condition 1 tab (hence the !) and had to mark the A to $A so it remained absolute as the formula traversed down the list.

austenr
04-16-2010, 01:53 PM
Glad to help