CODEWITHSUNDEEP
excelexcel-formula
user3831119
user3831119

Reputation: 23

Excel - VLOOKUP to search two columns

So here's what I'm trying to do. In my Backorder workbook, I have an Item # (this is either composed of a Manufacturer Part # or Retail Part #) and a Description of the part. Instead of hand typing in the description of each part, I was hoping to have it automatically populate based on the Item #. So I have a VLOOKUP equation that references my Inventory workbook, searching between the Manufacturer Part #, Retail Part #, and Part Description. It then returns the Part Description when found. My code looks something like this:

=VLOOKUP($G3, '[Finished Inventory.xlsx]Finish goods'!$E$2:$G$4000, 3, 0)

So I got this to work... but not without issue. The only rows returned with a proper value are the rows that contain a Manufacturer Part # (which is the first column searched through). But here's the weird part (to me at least): When I change my VLOOKUP range from 3 columns (Manufacturer Part #, Retail Part #, and Part Description) to 2 (Retail Part # and Part Description) I have much better results with having less #N/A cells. Issue is that now the parts with Manufacturer Part #s are left #N/A. Seems like VLOOKUP only searches through the first column of the search range.

So after typing this all out I'm realizing VLOOKUP only searches through one column... Can a solution be made such that it searches through two columns and returns a third?

Upvotes: 1

Views: 1335

Answers (1)

Aleksandar Misich
Aleksandar Misich

Reputation: 487

So to imitate a =VLOOKUP with two search columns and one result column, use something like this:

=(IFERROR(INDIRECT("C" & MATCH($D$12,A:A,0)),INDIRECT("C" & MATCH($D$12,B:B,0))))

D12 is your input cell.

INDIRECT will take a textual string and turn it into a cell reference, and Match will find you a Row in a column where the first instance of that value is presence.

In this example Column A is searched first, if that returns an error, Column B is searched, in either case, the result is the matched row in Column C

a - 1 - res1
b - 2 - res2
c - 3 - res3
d - 4 - res4

In the above example, "c" entered into D12 will give us "res3" in our formula output, where "2" will give us "res2" and so on.

Upvotes: 1

Related Questions