Lookup values

I have an app that will track my business mileage. I'd like it to have a drop down where I select the location for an event from a table and then have the Distance field updated automatically. Can't get the expression to select the distance value from the location table.

Found VLOOKUP, =VLOOKUP("BT", LookupLocation!a1:b47,2), but I need the value "BT" to be based on the dropdown for the location on the form.

1 Like

Hi @Andr-6e24 welcome to the community and thanks for the question! I suspect the reason your expression is not working is because "BT" is a string, whereas a dropdown is a rowlink to the entire row, those things may not match each other. While we are on this topic, may I suggest FindRow, it might work much better in this scenario: FindRow: A Better VLookup.

Yeah, been trying that too. The problem I'm having is that I can't reference the dropdown value inside the formula properly. I know it has to do with the quotes, but can't get the correct combination. Sample rows of data included below.

Here's what I'm trying: =findrow(LookupLocation, "LookupLocation[Location] = ""$[PickLocation]""")[Distance]

Location Distance
.Home (Walton) 2.1
Brookwood 34.6
BT 9.4
Cambridge 0
Camden County 0
Campbell 9.5
Chattahoochee 21

Hi @Andr-6e24 thanks for the response! Can you please try the following instead:

=findrow(LookupLocation, "LookupLocation[Location] = $[PickLocation]")

Your source needs to be like :


Thanks for sharing the screenshot @Andr-6e24, that helps! Two follow-up questions:

  1. When you open the app using "view app" link and use this screen, does it still show #VALUE! ?
  2. Where in the screenshot is $[PickLocation]? Is it a rowlink to the LookupLocation table?

Thanks Raja, that worked a treat!

1 Like

Raja above nailed the answer! Thanks for your help, sure I'll need it in the future!



This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.