Log In
Register
Home
Active Member
April 17, 2020
Offline
I have a table in the following format
I want to run a formula/query in column D to look in Column C and find the Item # in Column A matching it, and copy the text in column B of that Item #'s row into Column D of the searching row, otherwise leave it blank. The result would be...
I'm not constrained by which column the formula/query runs/executes in (it could be E or any other unused column), so long as the result ends up in Column D (in this example).
Why? I'm porting old DB3 data into a sql DB format for use in a php-based program using excel as the "middleware" (actually, I'm the middleware, excel is the tool). The actual table I'm working with is closer to 8000 rows, and, for example, row 5823 column C could well reference item # 2.
This is just one small part of the porting transformation process, and actually, the last one I have to complete, before I test upload. Alas, my head exploded on this one..
(And if this works, I have a 90,000 row table to tackle next, thank God for "fill down"..)
Disclaimer: I'm retired, been quite a few years since I've used excel, and this is a non-profit exercise in every sense of the phrase.
See AnswerActive Member
April 17, 2020
Offline
There were a couple of copy errors in the original vlookup2.jpg..
This is the corrected result
(Now you know why I want to "script" this...)
VIP
Trusted Members
December 7, 2016
Offline
Hello,
As you have posted this in the Power Query section, do you want a PQ solution or do you want a standard Excel formula using VLOOKUP?
The Excel formula solution. Write in cell D2 and drag down.
=IFNA(VLOOKUP($C2,$A$2:$B$11,2,0),””)
I can’t give you the PQ solution now as I am using a tablet.
Br,
Anders
Answers Post
Active Member
April 17, 2020
Offline
Thanks for the reply, Anders!
Actually, at this point all I want is a solution that works.. whatever does the trick! (I didn't think a standard excel formula would work, but if your suggested solution does, I'm more than good with that..).
OK, I see what confused me with this originally... adding a dollar sign in front of the column alphabet (C in this example), it locks the column only. This means that if I drag the formula containing $C2, the column won't change, but the row will.
To make sure I understand the formula =IFNA(VLOOKUP($C2,$A$2:$B$11,2,0),””), since the actual table I'm working with is far more extensive than this simple example, I believe the VLOOKUP part of the formula is saying:
lookup data "in column C of this row, for a match in the table A2 to B11 (column 1 of the defined table is default lookup), and copy column 2 data (of the matched row in the defined table). The "0" will force excel to return an N/A if there is nothing to match in Column C, correct?
If that is correct, then IFNA will return null "" if there is no match (i.e. column C has no lookup value, or there is no matching value in the specified range, and the vlookup result is "N/A").
Have I got that right?
-Dan
1 Guest(s)
