Using vlookup function in ArcMap?

Question

I am trying to automate a field calculation using ModelBuilder, using a GDB table to populate a feature class field given two conditions:

1. Traffic Count Value and
2. Posted Speed limit

I have created an IF statement via the field calculator but my manager does not want to have to rely on my VBA code if the table changes. The goal being changes to the table must be made in the table and updated via the table.

I am using ArcGIS Desktop 10.3 and ModelBuilder and Excel.

Example: If PSL is between 91-100 and Traffic Count is 500-999 the value is 1

Can some sort of vlookup function work?

• Rex: So you want the calculation to be done on the GDB table? Is all code off limits i.e. python? Also, even if you have a tool instead of code, if the structure of the table changes it will still break.
• Pat the forest: I am open to python, I am hoping the table stays the same # of c and # of rows but the values within can change. Currently trying to work with an update cursor
• Andy: @Pattheforest if your reference data are in Excel and you need to maintain/modify them in Excel, I would look at joining the Excel file to your feature class. How To: Join an Excel spreadsheet (.xls) to a feature class in ArcMap. Or do you mean that you are moving data from GDB table to Excel and then back? Might be good if you post samples of your GDB table and feature class tables.

Answer 1

You can use python.

Lookup table: Note that all intervals need to have a start and stop, with a '-' sign between them. So "53000-" need to be "53000-100000": enter image description here

import arcpy
lookuptable = r"D:\lookuptable.csv"
lookupview = 'lookupview'
arcpy.MakeTableView_management(in_table=lookuptable, out_view=lookupview)
columnlist = [f.name for f in arcpy.ListFields(lookupview)]
rowlist = [r[0] for r in arcpy.da.SearchCursor(lookupview,columnlist)]
del columnlist[0]
lookupvalues = [row for row in arcpy.da.SearchCursor(lookupview,columnlist)]
def givemearange(r):
 #'1-5' -> range(1,6)
 try:
 start,stop = r.split('-')
 return range(int(start),int(stop)+1)
 except:
 pass
def givemecolumnindex(columnvalue):
 columnindex = [i for i in range(len(columnlist)) if columnvalue in givemearange(columnlist[i])][0]
 return columnindex
def givemerowindex(rowvalue):
 rowindex = [i for i in range(len(rowlist)) if rowvalue in givemearange(rowlist[i])][0]
 return rowindex

You now can do:

lookupvalues[givemerowindex(4000)][givemecolumnindex(85)]
>>>9

enter image description here

Then use the da.UpdateCursor to read and write the values.

with arcpy.da.UpdateCursor(somefc,['Trafficcountfield','Speedlimitfield','field_to_calculate']) as cursor:
 for row in cursor:
 row[2] = lookupvalues[givemerowindex(row[0])][givemecolumnindex([row[1]])]
 cursor.updateRow(row)

Answer 2

The very basic solution is creating polygons from your break points. After that you can make XY layer from 2 relevant inputs and query polygons at points by using spatial join or similar tool. ArcGIS will complain about missing projection, who cares.

Picture below shows flood risk polygons I am using, where lookup values are flood depth and velocity:

enter image description here