make excel look up which cell to use in a formula
May 17, 2022 10:04 AM Subscribe
I want to write a formula in excel. THere is a sheet with rows that contain the text "a" "b" "c" "d". I want the formula to use column 2 from one of those rows. Which row? The row that matches the text in this other cell I'm going to point it to. I know this is a two step thing where I first make excel figure out which row and then write that formula into the larger formula. But I can't figure out the function to look up the right cell. The functions i find will tell me the the text is "third in the list" or whatever, not "Cell C2", (or better yet, Row 2) which is what I want.
If you're looking for 'return the thing in the second column where the first column matches my input' - Vlookup or Xlookup.
If you're looking for 'tell me what cell has the match?', then try this link for a copy-paste index/match formula.
If neither of those are what you mean, we might need an actual spreadsheet or screenshot.
posted by BekahVee at 10:37 AM on May 17, 2022
If you're looking for 'tell me what cell has the match?', then try this link for a copy-paste index/match formula.
If neither of those are what you mean, we might need an actual spreadsheet or screenshot.
posted by BekahVee at 10:37 AM on May 17, 2022
On mobile, but I think you're looking for the INDIRECT function.
posted by yeahlikethat at 12:21 PM on May 17, 2022 [1 favorite]
posted by yeahlikethat at 12:21 PM on May 17, 2022 [1 favorite]
I'm having trouble parsing what you want - more details might help.
One or both of INDEX / MATCH might help.
posted by lookoutbelow at 12:35 PM on May 17, 2022
One or both of INDEX / MATCH might help.
posted by lookoutbelow at 12:35 PM on May 17, 2022
+1 to yeahlikethat. In computer programming, this is called "deferred addressing."
It sounds like you may need to do a little extra work to get "C2" or whatever, so you'd do something like =indirect(concatenate(A1,"2")) so if A1 contains A, concatenate will give you "A2," and indirect will return the contents of cell A2.
posted by adamrice at 12:41 PM on May 17, 2022 [2 favorites]
It sounds like you may need to do a little extra work to get "C2" or whatever, so you'd do something like =indirect(concatenate(A1,"2")) so if A1 contains A, concatenate will give you "A2," and indirect will return the contents of cell A2.
posted by adamrice at 12:41 PM on May 17, 2022 [2 favorites]
What it sounds like you want: I have function1(function2(variables)) but I want function2 to be a different function depending on the value of some variable. Is that correct?
posted by TimHare at 7:24 PM on May 17, 2022
posted by TimHare at 7:24 PM on May 17, 2022
Yeah can you describe what you’re trying to do . . . differently? INDEX/MATCH is also my instinct but INDIRECT or even some nested IFS could be more what you’re looking for.
posted by aspersioncast at 8:10 PM on May 17, 2022
posted by aspersioncast at 8:10 PM on May 17, 2022
Agreed that more info is needed, but beyond the excellent suggestions already offered, check out the SWITCH function as well. I found it useful for a recent issue that sounds similar to yours.
posted by sapere aude at 12:32 PM on May 18, 2022
posted by sapere aude at 12:32 PM on May 18, 2022
ADDRESS might also be helpful.
posted by lookoutbelow at 2:05 PM on May 18, 2022
posted by lookoutbelow at 2:05 PM on May 18, 2022
Based on your description, I think that INDEX/MATCH is what I would use. You can find explanations online, but here's another one anyway.
I'll use this sample table as an example. It has two columns (A and B) and five rows. Row 1 is a header row.
MATCH(search-for, search-where, 0) will give you the row number that contains the "search-for" value within the "search-where" range. The third parameter sets what kind of match to look for. The zero means "exact match". In the example, MATCH("a", A:A, 0) will give you 2 because a is row 2 of column A:A.
So then you can combine those together into one function:
INDEX(B:B, MATCH("a", A:A, 0))
This will give you the value in column B in the row where the value in column A matches "a".
Some of the suggestions above, like VLOOKUP and XLOOKUP, will basically do the same thing with a single function instead of using two functions, but I've always found INDEX/MATCH to be more flexible.
posted by Lirp at 6:30 PM on May 18, 2022
I'll use this sample table as an example. It has two columns (A and B) and five rows. Row 1 is a header row.
A B 1 Label Value 2 a 35 3 b 201 4 c 86 5 d 15INDEX(range, row-index) will give you the value in a particular row within a particular range. In the example table above, INDEX(B:B, 2) will give you value "35" because that's the value in row 2 of column B:B.
MATCH(search-for, search-where, 0) will give you the row number that contains the "search-for" value within the "search-where" range. The third parameter sets what kind of match to look for. The zero means "exact match". In the example, MATCH("a", A:A, 0) will give you 2 because a is row 2 of column A:A.
So then you can combine those together into one function:
INDEX(B:B, MATCH("a", A:A, 0))
This will give you the value in column B in the row where the value in column A matches "a".
Some of the suggestions above, like VLOOKUP and XLOOKUP, will basically do the same thing with a single function instead of using two functions, but I've always found INDEX/MATCH to be more flexible.
posted by Lirp at 6:30 PM on May 18, 2022
« Older Ingesting news stories with as little trauma as... | Anger Management or Potential Abuser Newer »
This thread is closed to new comments.
posted by soelo at 10:17 AM on May 17, 2022 [3 favorites]