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.
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 :
=FINDROW(LookupLocation,"LookupLocation[Location]=%",$[PickLocation][Location])[Distance]
Thanks for sharing the screenshot @Andr-6e24, that helps! Two follow-up questions:
#VALUE! ?$[PickLocation]? Is it a rowlink to the LookupLocation table?Thanks Raja, that worked a treat!
Raja above nailed the answer! Thanks for your help, sure I'll need it in the future!
Nice!!!
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.