PING: Excel Gurus

[Dodgerchick]

I'd like a cell to automatically update the name of a person, based on a budget that's typed in a cell

For example:

Cell A1 is budget ZZ123-456
That budget belongs to Lorena Marks
I would like cell B1 to automatically update with Lorena Marks

Cell A2 is budget ZZ789-000
That budget belongs to Matt Damon
I would like B2 to automatically update with Matt Damon

We have a whole bunch names attached to different budgets, and having to type these long list of names is daunting. There's a separate tab where were pulling all this information from.

Hope it makes sense

Thanks!

[NobodyHere]

Something like this?

How to use the Excel VLOOKUP function | Exceljet

[joe the wanderer]

VLOOKUP works, but INDEX MATCH is more dynamic... With VLOOKUP, if you insert a column in between the start of your table and the column you want to reference, your formula will break (the column_index_number within your VLOOKUP won't update). INDEX MATCH, on the other hand, safely updates no matter where you insert columns.

[HerRealName]

Do you have MS Access? It seems like it would be a lot easier to make a query for this.

[Dodgerchick]

Originally Posted by HerRealName

Do you have MS Access? It seems like it would be a lot easier to make a query for this.

Sadly no

[Dodgerchick]

Originally Posted by NobodyHere

Something like this?

How to use the Excel VLOOKUP function | Exceljet

Hey, it totally worked! Took me a bit, but yes, that's it exactly. My entire department owes me lunch haha.

Thanks so much!

[Dodgerchick]

Okay, I lied, it didn't work.

=VLOOKUP(D147,'PT Contact List.03312021'!A2:C94,2,FALSE)

=VLOOKUP(D148,'PT Contact List.03312021'!A3:C95,2,FALSE)

I copied the formula, but I need the bold part to stay the same - A2:C94

Halp 😒

[NobodyHere]

Originally Posted by Dodgerchick

Okay, I lied, it didn't work.

=VLOOKUP(D147,'PT Contact List.03312021'!A2:C94,2,FALSE)

=VLOOKUP(D148,'PT Contact List.03312021'!A3:C95,2,FALSE)

I copied the formula, but I need the bold part to stay the same - A2:C94

Halp 😒

Instead of "A2:C94", use "$A$2:$C$94"

[Carman Bulldog]

Originally Posted by NobodyHere

Instead of "A2:C94", use "$A$2:$C$94"

Or hit F4 when you have the cursor on A2:C94. Much quicker than having to manually punch in all of the dollar signs if you are doing quite a few of these.

[Bobble]

Originally Posted by Carman Bulldog

Or hit F4 when you have the cursor on A2:C94. Much quicker than having to manually punch in all of the dollar signs if you are doing quite a few of these.

F4 cycles through, so hitting it once gives you $A$2:$C$94. A second time gives you $A2:$C94, "locking" the columns. A third time gives you A$2:C$94, "locking" the rows. Well, I'm not sure that's the right order but multiple F4s cycle through your options.

Secondly, I'm with joe in that the cool kids use INDEX-MATCH. It's more powerful and robust. In your case, it would be something like =INDEX($B$2:$B$94,MATCH(D147,$A$2:$A$94)) . From inside out, it means MATCH the contents of D147 with the source stuff in $A$2:$A$94 and secretly count how far down you had to go. Now, (with INDEX) count down that many in the return stuff in $B$2:$B$94 and give me that value.

You don't have to have the columns side-by-side nor even in the same rows nor even on the same sheet nor in column form (but it gets tricky -- you have to figure out how it does the counting). And, if anyone inserts and moves stuff, it still works fine. You have to do something clever with your column number if you use VLOOKUP to get that robustness.

[Vince, Pt. II]

Index - Match is amazing. It's a little wonky to get your head around the first few times, but once you have it, it's incredible.

That first range of cells is the data you are looking for - what you want to show up when you're done. The individual cell reference is what you're looking up to find it. The second range of cells is where your key data value you are looking up is.

[Vince, Pt. II]

In your case, you have a table somewhere with both Budget and the name of who it belongs to. And you have a separate list with just the budget. So your three values in the cell:

=Index([range of names in table],match(A1,[range of budgets in table],0)

If you put this formula in cell B1 on the tab where your list of budgets is that needs names, then use those $ to make the range for names and budgets an absolute reference, you can copy the formula down to each row in your list and it will work.

[Carman Bulldog]

Originally Posted by Bobble

F4 cycles through, so hitting it once gives you $A$2:$C$94. A second time gives you $A2:$C94, "locking" the columns. A third time gives you A$2:C$94, "locking" the rows. Well, I'm not sure that's the right order but multiple F4s cycle through your options.

Yeah, I thought of mentioning this. I didn't want it to get too confusing.

Agree with everyone else though about INDEX-MATCH. I mean, VLOOKUP is okay for what it is, but it only goes one direction. INDEX-MATCH is much more powerful and overall a better skill to have familiarity with.

[21C]

I haven't gotten around to using it myself yet but I thought that XLOOKUP is the new preferred option for these sorts of things.

XLOOKUP function - Office Support

[Vince, Pt. II]

That's pretty slick. Index/match all in one, with a built in iferror for fun. That'll make a lot of my formulas way cleaner.

[henry296]

I just used xlookup for the first time. I had my lookup value in Column D and the value I needed was in column B. With vlookup, i would have to copy my lookup column and now I just set each column as the lookup and return array and it worked perfectly.