I receive Excel formatted returns from a DB (I have no control over) that present as in the pic below. I have to compile a 500 row report removing Site Code and replacing with the Site Name, in addition to adding the corresponding City and State in Excel. Rather than writing out an IF statement 300 times over for each value, what are the alternatives? I'd like to select the entire Site Code column, match it to the Name, and match it to the City and State.

I can work out an IF statement for Site, City, and State, and add nested IF statements, but writing it out 300 times X 3 is not the way to go. Example: =IF(C3=F3, G3, IF(C3=F4, G4...))

I cannot find a way to select the entire F column and match it the the G column. I've tried F1:F300 etc. but cannot get it to work.

Any help is appreciated. If I'm looking for a different function let me know, I'd gladly do the research before posting again.

Sample Table

CodePudding user response：

I believe C19 should read "RR45" not "Name 1". If this is the case then you can use a lookup formula (Vlookup, Xlookup(365 only), Match & Index), so that you can avoid typing a "If" 300 times. You should always try to avoid long nested formulas to minimize errors.

Here is a simple Vlookup solution:

In cell D18: =VLOOKUP(C3,$F$3:$I$300,3,0) In cell E18: =VLOOKUP(C3,$F$3:$I$300,4,0)

Now apply the formula till the bottom of your table.