“Google Sheet” ,Compare two columns & return value

问题: 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

  • 发表于 2019-03-13 00:17
  • 阅读 ( 174 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除