Excel – Search for value, return value in adjacent cell

excel

I am not good with the advanced Excel stuff, and I've got something which appears to be a VLOOKUP question (which I have never used before).

The situation is such. I need to search for a specific value in six columns of a sheet and return the value of the cell adjacent to the right. So far I have a-googled many options, none of which work, although I did get ISERROR to work and tell me there's no match (which is wrong).

In fact, here is that one, which seems most promising:

=IF(ISERROR(MATCH(2013,I2:N433,0)), "No Match", VLOOKUP(2013,J2:N433,+1,FALSE))

Here's is what I think it is doing:

  • The IF gets it rolling
  • ISERROR is returning "No match" if the criteria are not met…
  • …which should be the MATCH section, searching for 2013 in the range J2:N433, 0 for exact
  • …and if the criteria are met, then the VLOOKUP is looking for 2013 in the array J2:N433
  • …putting in the +1 to return the value of one column over
  • …and FALSE used for an exact match

So I think the +1 is a sticking point. I read a post on here or excelforum.com saying that when using a MATCH, +1 increments one column, but that just seems too loose for Excel.

To be honest, I have read a lot of posts over the past two hours, and they're all jumbled in my head. I figured I'd just head right to here and see who can un-jumble my ideas.

Thanks very much. I am using Mac Excel 2011.

EDIT: Here is a chunk of my data.

2011    6   2012    5   2013    6   2014    57
2011    6   2012    5   2013    6   2014    57
2011    11  2012    10  2013    11  2014    62
2010    11  2011    10  2012    11  2013    62
2010    11  2011    10  2012    11  2013    62

What I wish to see is be able to make another column that finds the 2013s then displays the value to their right, such as 6, 11, 62, etc.

EDIT 2: I added the above data upon Werner's request as you don't get new lines in comments. When I did that, I apparently lost the answer thread…? Annoying. Sorry, I didn't think that would happen.

Best Answer

Based on Excel 2007 (so beware!), first I'd suggest copying your data (6 or 8 columns, I'm not sure - but it does not really matter) and pasting it into Word as unformatted text. Select, Insert > Tables - Table, Convert Text to Table with Number of columns: 2 and Separate text at Tabs, OK. Hopefully you can copy this back into Excel (say A2) and have a more ordered data set.

Add column labels (say Year and Value) and create a PivotTable (say in D2) from ColumnA:B with Value for Column Labels, Year for Row Labels and Value in the Values field. Somewhere off to the right (depending upon how many unique values there are in your data set) put in Row4: 

=IF(ISBLANK(E4),"",E$3)

Copy across and down to suit.

Based on the sample, Data, Data Tools - Remove Duplicates might help to weed out some repetition and it might be viable to insert cells manually to align the columns by year before extracting unique values for each column in turn (Data > Sort & Filter - Advanced, Copy to another location/Unique records only)..

Related Topic