I would like a field to auto-populate when I select data from another field. I understand I could do this with an "IF" function.

However, the data from the initiating field is a long list filled via data verification. If I select an item from the first list, I would like it to populate with the corresponding number from the adjacent field.

Sample Sheet: https://docs.google.com/spreadsheets/d/1x4GhIv4ptDkM-IpfdtLH2cFtfKk5gzYTYo4ETKhY68Y/edit?usp=sharing

For example: Using Data Verification, 'Template"!A2 is populated with the list from 'Tax Codes and Rates' Column A. If I select 05-005 (which is 'Tax Codes and Rates'!A23) is there a formula that would automatically 'Tax Codes and Rates'B23?


1 Answer 1


Perfect use case for the VLOOKUP function.

=VLOOKUP(A2, 'Tax Codes and Rates'!A2:B35, 2, FALSE)
  • Thank you, @David Tan! Nov 30, 2022 at 19:58
  • I have a follow up question to this. How do I use this formula in successive rows? Every time I paste the formula, the lookup rate changes. For example, if I start with =VLOOKUP(G2,Inclusions!A2:B8,2,FALSE) and paste it in the field immediately below, it changes to =VLOOKUP(G3,Inclusions!A3:B9,2,FALSE) Or if I paste the formula as text, the reference cell doesn't change: =VLOOKUP(G2,Inclusions!A2:B8,2,FALSE) stays the same, when it should be =VLOOKUP(G3,Inclusions!A2:B8,2,FALSE) Dec 20, 2022 at 17:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.