0

Two tabs in an Excel 2013 spreadsheet. One tab is named Homes Data and the other tab is named Ratings Data. Column O in the Homes Data tab is a list of postal codes and column G in the Ratings Data tab also contains a list of postcodes. Column L on the Ratings Data tab contains a list of Ratings.

I am trying to create a match/lookup/macro that identifies matching postcodes on the two tabs and then looks up the corresponding entry in Ratings Data column L and then creates a new column AG on the Homes Data tab to record the data alongside the matching postcode.

Homes Data Tab

Ratings Data Tab

Improve this question
7
  • Can you post a screenshot of what you'd like the final result to look like? What have you tried so far?
    – JaredT
    May 3, 2016 at 16:58
  • I cannot copy as data is confidential. Essentially the Homes Data tab is a list of every property and the Ratings Data tab is a rating of most of the properties. For each unique property in column K of the Ratings Data tab there is a list of categories "Overall, Safe, Effective, Caring, Responsive, Well-led" for each property and column L includes the rating awarded under each.
    – Maximusreborn
    May 4, 2016 at 9:21
  • A lookup is simple, but if you want to "create a new column" you are outside the bounds of a formula field and will need some vba. =INDEX($Return$Range,MATCH(LookupCellRef,$Lookup$Range,0))
    – JaredT
    May 4, 2016 at 14:39
  • Thanks. When I said "create a new column" I actually meant just to copy the corresponding data into a new column.
    – Maximusreborn
    May 5, 2016 at 8:19
  • I've added screenshots into the original post to hopefully better explain what I'm trying to do. Essentially I am trying to derive a formula to retrieve the entry highlighted yellow on the Ratings Data tab into the cell highlighted yellow on the Homes Data tab. To do this I tried combining a LOOKUP function with two MATCH functions using the AND function but that obviously doesn't work as one cell cant match both functions.
    – Maximusreborn
    May 5, 2016 at 9:02

1 Answer 1

Reset to default
0

Sounds as if you need a relatively simple lookup, in which case:

You can do lookups in a couple of ways:

  • VLOOKUP() which is easy to learn but limited use

  • INDEX(MATCH()) which is harder to learn but more flexible

Depends on your preference as to which you use.

The easier version is the VLOOKUP, in which case you'd put this formula into column AG of the Homes sheet:

=VLOOKUP(

as soon as you get this far, Excel will help (a bit!) by telling you what you need next - the hovery text will say:

VLOOKUP(lookup_value , table_array , col_index_num , [range_lookup])

lookup_value is your postcode on Homes sheet

table_array is where you're looking it up, which is columns G to L of your Ratings sheet - the limitation of VLOOKUP is that the column you're looking up (postcode on Ratings sheet) has to be to the left of the one you want to know about (rating on Ratings sheet) which fortunately for you, it already is. It also has to be the left-most column, which is why we're selecting columns G to L, not A to L, so that column G (postcode) is on the left of what we're looking at.

col_index_num is the number'th column you want back. So G = 1, H = 2, I = 3, J = 4, K = 5, L = 6. So you want 6 since you want what's in column L.

range_lookup is optional but actually important. FALSE means it'll do an exact match, whereas TRUE (which bizarrely is the default setting) means it'll do an approx match. If your postcode is 1245, you don't want it to find 1240 instead if 1245 isn't there, so you want FALSE here.

So that gives you, if you're typing into cell AG2:

=VLOOKUP(O2 , 'Ratings Data'!G:L , 6 , FALSE)

That will bring back the rating for the postcodes on Homes tab. If it can't find the postcode, you'll get an NA error, which you can deal with using IFNA() or IFERROR() around your VLOOKUP. You also might want to use $ signs so that if you copy the formula elsewhere later it still looks at columns G to L each time.

eg

=IFERROR(VLOOKUP(O2 , 'Ratings Data'!$G:$L , 6 , FALSE),"Not found")

INDEX(MATCH()) works similarly and techies tend to prefer it, but most non-techie people I work with find it a step too far to learn and prefer to start with VLOOKUP which does the job pretty well 99% of the time for most normal work applications. If you're one of the techie people then search google for INDEX MATCH and there are probably some decent explanations ;-)

Improve this answer
3
  • Thank you! I've posted screenshots in the original post and think that the INDEX(MATCH()) function is what I need as I need a bit more flexibility on this occasion. I'm getting there but still cant get it to quite work.
    – Maximusreborn
    May 5, 2016 at 9:47
  • Cracked it by using the array in the thread comment above. Really appreciate the help.
    – Maximusreborn
    May 5, 2016 at 10:13
  • I would think VLOOKUP should work fine for you. Put this into AG2 =VLOOKUP($O2 , Ratings!$G:$K , 5, FALSE) And then copy it down column AG. ie: - look up what's in column O - look that value up in columns G-K of the other tab (it looks up in the left column of what you've selected, which is column G) - take the 5th column of data that you find (column G is 1, H is 2 (even though it's hidden), I is 3, J is 4, J is 5) - FALSE means get exact matches, ie if the postcode isn't there, don't just get the nearest match =IFERROR(VLOOKUP($O2,Ratings!$G:$K,5,FALSE),"No Rating")
    – psymann
    Aug 2, 2017 at 3:18

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .