0

When I work with excel files its very often to insert new data to a table, but copy and pasting each value one by one the be in the same row with the key table takes a lot of time and effort. Is there any way to do it without doing it one by one?

I'll try to explain my problem with a simple example: This is (for example) the table I am working on-

Main table

and lets say that I have found data from some data base that has 4 number that are already in the Main table:

New data

My final goal is to have them inside the main table and having the final table:

Final Main table

What I have done in this case is locating each phone number from the new data and then inserting the ID in the same line.

Improve this question
2
  • If more than one or two pages often better to use a database and follow (most of) Codd's rules. en.wikipedia.org/wiki/Codd%27s_12_rules Specifically you need unique ID and insert or update based on that ID.
    – oldfred
    Mar 6, 2020 at 14:49
  • @oldfred What do you mean when you say pages? In my case the table is being modified always and a Database for it will take too many time and recourses.
    – Ohav
    Mar 6, 2020 at 15:00

1 Answer 1

Reset to default
2

You can use a Vlookup function in Excel to do this.

enter image description here

Where you need to input the new value (SomeID) you need to enter a formula instead. You can find documentation on VLOOKUP here.

enter image description here

In the example above, A6 refers to the value you want to "lookup". You should lookup a value in the same row normally. A6 in this example is 13313802.

The next argument in the Formula is the new table of information to check your value against. The cell references are surrounded in $ absolute references so when you reuse the functionality in the rest of the cells in column D, the look up table reference doesn't move.

The next value is "2". This refers to the column that should be returned to the cell if the value is found in the table.

The final value is "False". If you use "True" an approximate match will found (you don't want this).

It's also important to note, in order for this to work ensure your new Data is ordered A-Z beforehand or the Vlookup won't find your value.

Improve this answer
4
  • might aswell include IFERROR to remove N/As
    – PeterH
    Mar 6, 2020 at 15:41
  • Thanks Dom. some questions: 1) What should be oredered A-Z, the keys(Phone) or the values(someID) in me new data? 2) I ran the function succesfully, but is there a way to run this function on all of the Keys in the new data? something that won't require me to run it each time?
    – Ohav
    Mar 6, 2020 at 18:28
  • 1) The values you are checking against need to be ordered. In your case it's the "new data" should be ordered by column 1. In my example (where new data is in range G5:H9 you need to order that data by the values in column 1 (G5).
    – Dom
    Mar 19, 2020 at 22:10
  • 2) I am not sure what this means, in the example, it looks up all the values in the new data already. If you mean, can it refer to all the "new data" you keep adding. Then yes, you need to change the function to take into account more rows at the bottom where you will add new data. In my example, if you were pasting more new data, you would extend the function $G$5:$H$100 add as many as you want...
    – Dom
    Mar 19, 2020 at 22:13

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .