问题:
I want to do some function like sql joint table. But I really dont know if google sheet can do it.
I want to compare two columns and return the cost, how can I perform it...
可以将文章内容翻译成中文,广告屏蔽插件会导致该功能失效:
问题:
I want to do some function like sql joint table. But I really dont know if google sheet can do it.
I want to compare two columns and return the cost, how can I perform it?
ColA ColB ColG ColH
Type Cost Type Cost
A 100 B
B 200 E
C 300 D
D 400 A
E 500 C
I want colH would show colB data when comparing colG to colA, is there any function in google sheet that can do it? It's just like the joint table function sql table.
Thanks
回答1:
Vertical Lookup function VLOOKUP
is what you want to solve this problem.
VLOOKUP(search_key, range, index, [is_sorted])
Pass the following parameters:
search_key
is the value to search in the costs array, column G in you case.
range
is the costs array
index
is the column to return in the costs array, the second one
is_sorted
should be set to TRUE
, as you do not want incorrect costs to be returned, if the value in column G does not exist in the costs array.
Hence, the formula to use in H2 is:
=VLOOKUP(G2,A1:B6,2,false)
Then, in order to allow you to use the same formula in all cells inside the column H, you need you make sure that it is always A1:B6
which is looked up as the costs array. If you copy paste the formula above from H2 to H3, it will adapt it and move the array one line below (A2:B7), which is not what you want. To solve this, you should use the dollar sign to lock the costs array range. This article explains how it works.
The formula then becomes the one below, and you can copy/paste it inside the whole column H:
=VLOOKUP(G2,$A$1:$B$6,2,false)
Demo sheet with the solution in place: https://docs.google.com/spreadsheets/d/1rzvCmgTi1DgA5Dbwb_iwKYc5RW_9uwFs2sVR0PbN7Ic/edit?usp=sharing