匹配两个电子表格中的两列并从其中一个电子表格中获取数据 [英] Matching two columns from two spreadsheets and grabbing data from one of the spreadsheets
问题描述
我需要在两个不同电子表格的两列之间匹配值(文本).
I need to match values (texts) between two columns on two different spreadsheets.
为了方便起见,我把它们放在一张纸上.
To make it easy I brought them together into one sheet.
目前,我将第二张(第二张)中的 A、B、C、D、E 和 F 列粘贴到第一张(第一张)中.在 F 列之后,一切都与原始(第一张)相关.
Currently I have Column A, B, C, D, E and F from the second (2nd) sheet pasted into the first (1st) sheet. After Column F, everything is related to the original (1st sheet).
我正在尝试将 A 列(第二张工作表数据)中的数据与 G 列(第一张工作表数据)中的数据进行匹配.如果有匹配项,我想获取与 G 列相关的整个行/列,在这种情况下是来自第一张表的信息.这是我使用的公式,它找到了两列之间的差异,但我不知道如何将其与列 G 匹配,并使用它获取详细行.
I am trying to match data from Column A (2nd sheet data) to Column G (1st sheet data). If there is a match, I want to grab the entire row/columns related to Column G, in this case the information from the 1st Sheet. This is the formula I use and it finds the differences between the two columns, but I cannot figure out how to match it to Column G, and obtain the detailed row with it.
=IF(COUNTIF($A:$A, $G5)=0, "Doesn't Exist", "")
请注意,匹配的两列数据相似但分散.
Just to note, the two columns being matched have similar data but are scattered.
推荐答案
看起来您的意思是要将第 2 个工作表的行连接到第一个工作表的行.对?
我不明白你为什么将数据合并到一张纸上.把它分开放在两张纸上.
如果G列第一张表和A列第二张表相同,那么您可以使用它进行匹配.
将公式放在第 H 列第一张表中.使用此公式,您将从 B 列第二张工作表中获取数据到 H 列第一张工作表.=index(sheet2!B1:B3;match(sheet1!G1;sheet2!A1:A3;0))
It looks like you mean you want to join row from 2nd sheet to row first sheet. Right?
I dont't understand why you merge data to one sheet. Keep it seperate on two sheets.
If column G 1st sheet and column A 2nd sheet are the same then you can use this to match.
Place formula in column H 1st sheet.
With this formula you wil fetch data from column B 2nd sheet to column H 1st sheet.
=index(sheet2!B1:B3;match(sheet1!G1;sheet2!A1:A3;0))
在下一列重复公式,I、J、K、L...等
Repeat formula in next columns, I, J, K, L...etc
这篇关于匹配两个电子表格中的两列并从其中一个电子表格中获取数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!