Google表格:用于检查列，匹配单元格，返回相邻单元格的公式 [英] Google Sheets: Formula to check column, match cell, return adjacent cell

问题描述

我有一个电子表格，其中列出了输出到各天的交货时间表，并希望从标题为餐饮的工作表中包含这几天的一组新数据.

I have a spreadsheet with a schedule of deliveries output to individual days and would like to include a new set of data on these days from a sheet titled Catering.

我有一个有效的条件，但是我无法弄清楚如何使它成为一个更广泛的公式.

I have a condition that works, but I'm not able to figure out how to make it a broader formula.

=IF(B1=Catering!A1, Catering!B1, "No known catering")

这会将当前工作表中B1中的日期与A1中餐饮表中的日期进行比较，并返回B1的值.

This checks a date in B1 on current sheet against a date on the Catering sheet in A1 and returns the value of B1.

我想做的是将当前工作表B1与餐饮A:A进行匹配，如果存在匹配，则返回相邻的单元格值，即.如果当前工作表B1与餐饮A:A中的餐饮A5相匹配，则返回餐饮B5.

What I would like it to do is match current sheet B1 against Catering A:A and if there is a match, return the adjacent cell value ie. if current sheet B1 matches Catering A5 from Catering A:A, return Catering B5.

推荐答案

使用VLOOKUP，例如:

=ARRAYFORMULA(IFERROR(VLOOKUP(B1:B, Catering!A1:B, 2, 0), "No known catering"))

或日期和匹配项:

=ARRAYFORMULA(IFERROR(VLOOKUP(B1:B, Catering!A1:B, {1,2}, 0), "No known catering"))

或者如果您不需要数组:

or if you don't need array:

=IFERROR(VLOOKUP(B1, Catering!A1:B, 2, 0), "No known catering")

这篇关于Google表格:用于检查列，匹配单元格，返回相邻单元格的公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！