3 ms·

Wait really? Well if thats the case, making VLookups be able to use any column as an input or output, rather than being limited to having the input on the left

by djbebs 4y ago

Wait really? Well if thats the case, making VLookups be able to use any column as an input or output, rather than being limited to having the input on the left and the output on the right. Ideally I'd be able to have 2 additional parameters, one that would indicate the column where the input value is to be found, and another that would indicated the column from which the output value would come from. I know there are some work arounds, but this would really simplify my life!

djbebs 4y ago
Well half that is already done(the output!)
shiftspace-- 4y ago
Might be what you’re after: https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929 https://support.microsoft.com/en-us/office/xlookup-function-...
Cogito 4y ago
You may already know this, but if not and for others: my Excel files became a lot more manageable, maintainable, and readable when I switched from _LOOKUP() functions to the INDEX-MATCH pattern, and in particular using the pattern with Tables. Basically - Put the data to be looked up in a named Excel Table with a short, meaningful name. `data` is a good default name, but something with more meaning is better. - Put the lookup 'results' in an Excel Table (naming optional but recommended). The output will be one column of the table, with one of the other columns used as input. - Construct the output formula like `=INDEX(data[[value_column]], MATCH([@[input_column]], data[[lookup_column]], 0))`. - (Optional) Put all formulas at the far right of the results table, so that you can copy new data into the left side easily without overwriting the formulas. The MATCH finds the first row in `data` that has the lookup value from `input_column` (in the current table) in the `lookup_column` (in the data table). The INDEX grabs the value from `value_column` of the `data` table in that row. Using Excel Tables helps by making the formulas more readable, and resistant to change. If new columns are added or removed the formulas continue to work (not true for how most _LOOKUP formulas are written), and the formula gets copied down to new rows as you add them. You can switch to row lookups if needed (though you can't really use Excel Tables anymore) if you need dynamic lookups you can specify both row and column as MATCHes in the INDEX formula (and INDEX against the whole `data` table instead of just one column). Something like `=INDEX(data, MATCH([@[input_column]], data[[lookup_column]], 0), MATCH([@[column_name]], data[#Headers], 0))`.
- rr888 4y ago
  I was going to reply with this too. Joel Spolsky explains it well https://youtu.be/0nbkaYsR94c?t=1824 https://youtu.be/0nbkaYsR94c?t=1824

by djbebs 4y ago

djbebs 4y ago

Well half that is already done(the output!)

shiftspace-- 4y ago

Might be what you’re after: https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929 https://support.microsoft.com/en-us/office/xlookup-function-...

Cogito 4y ago

You may already know this, but if not and for others: my Excel files became a lot more manageable, maintainable, and readable when I switched from _LOOKUP() functions to the INDEX-MATCH pattern, and in particular using the pattern with Tables. Basically - Put the data to be looked up in a named Excel Table with a short, meaningful name. `data` is a good default name, but something with more meaning is better. - Put the lookup 'results' in an Excel Table (naming optional but recommended). The output will be one column of the table, with one of the other columns used as input. - Construct the output formula like `=INDEX(data[[value_column]], MATCH([@[input_column]], data[[lookup_column]], 0))`. - (Optional) Put all formulas at the far right of the results table, so that you can copy new data into the left side easily without overwriting the formulas. The MATCH finds the first row in `data` that has the lookup value from `input_column` (in the current table) in the `lookup_column` (in the data table). The INDEX grabs the value from `value_column` of the `data` table in that row. Using Excel Tables helps by making the formulas more readable, and resistant to change. If new columns are added or removed the formulas continue to work (not true for how most _LOOKUP formulas are written), and the formula gets copied down to new rows as you add them. You can switch to row lookups if needed (though you can't really use Excel Tables anymore) if you need dynamic lookups you can specify both row and column as MATCHes in the INDEX formula (and INDEX against the whole `data` table instead of just one column). Something like `=INDEX(data, MATCH([@[input_column]], data[[lookup_column]], 0), MATCH([@[column_name]], data[#Headers], 0))`.

rr888 4y ago
I was going to reply with this too. Joel Spolsky explains it well https://youtu.be/0nbkaYsR94c?t=1824 https://youtu.be/0nbkaYsR94c?t=1824