Search function instead of if statements

[rfr3sh]

I am trying to automate my model, and the only thing I can think of is creating if statements

ie if I type in Wizards in Cell A30 then it will pull the necessary information from the spreadsheet above to calculate what I want it to, the only problem is there are 30 teams in the league so an If statement is kind of tedious

something like if(A30='Wizards' then B30 = ... etc) but do that 30 times

I was curious if there is some sort of search function or anything in Excel that would search from A2:A31 for a specific string, and if it is found then apply calculations?

Or would I have to code this?

[Maverick22]

This screams macro to me.

Record a macro for this one team.

Then duplicate for the rest?

The initial idea i was to. Record a macro, to pull a team. and create a new sheet?
Then modify the macro to do it for each of the 30 teams?
You can do w/e model voodoo magic you do.
Then record another macro to delete them you are done.

Essentially creating temporary tables.

[xbalto]

vlookup/hlookup

[rfr3sh]

amazing

[Flying Dutchman]

Yeah, vlookup is the cure for a ton of ills.

[Flying Dutchman]

BUT, you got to make sure your lookup list is sorted lowest to highest!!!! most important!

[rfr3sh]

BUT, you got to make sure your lookup list is sorted lowest to highest!!!! most important!

what if it is a string? and why is this

[xbalto]

Yeah, vlookup has some strange behavior, and I think the best way to avoid it is to pass "0" or "FALSE" as the fourth argument to vlookup (i.e. as "range_lookup" in VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)). This will make sure it only returns exact matches.

[mminkovski]

yes, vlookup returns exact matches when 0 is at the end

if you're looking for match in more than 1 cell then concatenate them first

[rfr3sh]

yeah I had it set to false, and it was returning the correct data in an unsorted list, thanks again