CODEWITHSUNDEEP
excelexcel-2007
Peter C
Peter C

Reputation: 553

Excel - Multiple values and need to add value from lookup

Apologies if this has been asked before, but after looking for ages, I have not found a solution. I am used to relational databases where this sort of thing is easy, but I've not tried in Excel before.

I have some archaeological finds data with multiple types of find at the same map coordinates. I need to be able to associate the coords to the find locations.

This is a tiny snippet of the finds data.

Finds

You will see here that there are 2 types of finds at locations, 239 and 281. I have a lookup table like this:

Lookup

and what I need to achieve is:

Result

I've looked into LOOKUP, VLOOKUP and HLOOKUP, but these do not solve this issue.

Upvotes: 1

Views: 164

Answers (3)

teylyn
teylyn

Reputation: 35990

You can use Vlookup. What have you tried? Why do you think it is not working? With your data sample, you can use

=vlookup(A2,Table2,2,False) for Easting

=vlookup(A2,Table2,3,False) for Northing

Table2 is the second table in your screenshot, consisting of the three columns FindLoc, Easting and Northing.

Using Vlookup with "False" as the last argument will return an exact match, so 238 for example will return an error. Omitting the last parameter, as suggested in another answer, will return an approximate match and a lookup for 238 will happily return the value for 224, since that is the next smallest value in the table.

Upvotes: 2

Floris
Floris

Reputation: 46435

VLOOKUP should solve your issue just fine. If we call the table (range) of values with locations locTable, the you can add in the cell below your Easting header (which I will assume is C1):

=VLOOKUP(A2, locTable, 2, FALSE)

And in cell D1

=VLOOKUP(A2, locTable, 3, FALSE)

Then select C2:D2 and double-click the little drag handle in the bottom right hand corner to copy it down to all the rows below.

Note - this does assume your location data is sorted. If it is not, you need to use a combination of MATCH and INDEX.

Upvotes: 1

chris neilsen
chris neilsen

Reputation: 53136

You can use Get External Data (dispite its name) to set up a connection in a workbook to query data from itself. Data tab/ Use From Other Sources/ From Microsoft Query to connect to Excel. Build the query in and return data to Excel.

I find it works best if you configure your data as Tables (Insert tab/ Table)

Tested in Excel 2010 (don't have 2007 to hand)

End result:

enter image description here

Upvotes: 0

Related Questions