使用vlookup或索引/匹配更新Excel中的价目表 [英] Updating Price List in Excel with vlookup or Index/Match

问题描述

我是图书批发商，需要不断更新价格和库存清单，以便以excel格式与一些客户共享；也可用于导入网站.

I'm a book wholesaler and need to update my price and stock list constantly to share with some customers in excel format; also to use in importing to websites.

如上图所示，我需要的是D列的公式，以在A列的C2中搜索SKU以进行精确匹配；将值从B返回到D列中的相应单元格.

As in the image above, what I need is a formula for Column D to search for SKU in C2 in A-column for an exact match; return the value from B to the corresponding cell in D column.

我搜索了无数主题，甚至在前几周都找到了解决方案，但是每次我使用相同的公式时，都会遇到N/A或REF错误.

I have searched countless topics, even found a solution in previous weeks but every time I use the same formula I get N/A or REF error.

TIA

推荐答案

欢迎使用SO.您的公式很好，但是您正在将数字和文本混合在一起.在A列中，您列出的代码存储为TEXT(请注意，它们在单元格中左对齐)，但C列中的值存储为NUMBERS(请注意，它们在单元格中右对齐).因此，Excel正在查找该NUMBER，但在A列中没有找到匹配的NUMBER，并返回N/A.

Welcome to SO. Yor formula is good, but you are mixing numbers with text. In column A, the codes you are listing are stored as TEXT (note that they are left aligned inside cell), but the values you have in column C are stored as NUMBERS (note that they are right aligned insided cell). So Excel is looking for that NUMBER, but it finds no NUMBER in column A that matches, and returns N/A.

因此，在搜索之前，让我们将C列中的数字转换为文本，然后看看会发生什么.尝试这样的事情:

So before searching, let's convert the number in column C to a text, and let's see what happens. Try something like this:

=VLOOKUP(TEXT(C2;"@");$A$2:$B$2349;2;FALSE)

希望您可以使其适应您的需求.

Hope you can adapt this to your needs.

这篇关于使用vlookup或索引/匹配更新Excel中的价目表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！