"Google Sheet" ,Compare two columns & return value

Question

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

Do not change perfectly useful textual information into a picture of text. Helping you is easier if the needed information is available in a shape which can be copied easily, searched, edited, ... — Yunnosch
Have you tried the Conditional formatting? see this zapier.com/blog/conditional-formatting-google-sheets — Umer
I tried =lookup & =IF but it won't show the exact result i want, — LazyP
Can you share the formula that you were using? (I think it can be done with either IF or with the Conditional Formatting) — Umer
Welcome to Stackoverflow. Next time, you may try to create a Minimal, Complete, and Verifiable example, this makes it easier to give you some help ;) — Mehdi

Mehdi Mehdi · Accepted Answer · 2019-03-08T08:08:03

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

"Google Sheet" ,Compare two columns & return value

1 Answers