Using large amounts of IF statements

**Malteser** · 04-05-2015, 08:15 PM

I'm using concatenated If statements, in this format: =IF(B5="A",I32,"")&IF(B5="B",I39,"")&IF(B5="C",I45,""). However, there's a limit of 30 or so, and I'd like to use more than that, over 100. Is there any way to get around this, maybe by using another formula? Thanks.

**Tony Valko** · 04-05-2015, 08:19 PM

Create a 2 column table and use a lookup formula.

See this:

http://contextures.com/xlFunctions02.html

**FDibbins** · 04-05-2015, 08:19 PM

Hi, welcome to the forum

I suggest you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

edit: Tony, there are a bunch of &'s in there, so it will need more than just a lookup table (I think?)

**Malteser** · 04-05-2015, 08:30 PM

Thanks for the answers. I'll take a look at the link.

What I have are a list of football teams and the amount of goals they score, so like (Arsenal, 1.9), (Aston Villa, 0.8), and so on. What I want is to be able to input the name of the team in one cell (B90), and have the number of goals displayed for that team in another cell. So, the formula goes like this: =IF(B90="Arsenal",H117,"")&IF(B90="Aston Villa",H124,"")&IF(B90="Burnley",H130,""), but after a certain amount of If statements, I can't add any more.

**daddylonglegs** · 04-05-2015, 08:40 PM

If you have the number of goals for Arsenal in H117 and number of goals for Aston Villa in H124 etc. then presumably the club names are also in column H? Is Arsenal in H116 or H115 or in an adjacent column? If that's the case you can probably use an INDEX/MATCH formula to pull the relevant goals number depending on the team, e.g. if the team name is always 2 cells above the goals figure then you could use this formula

=INDEX(H$117:H$1002,MATCH(B90,H$115:H$1000,0))

Note that the first range is "offset" by 2 from the second range

**FDibbins** · 04-05-2015, 08:47 PM

Maybe Tony was correct after all....

	A	B
1	Team
2	aa	1.9
3	bb	0.8
4	cc	2.1
5
6	bb
7	0.8

A7=VLOOKUP(A6,$A$2:$B$4,2,0)

**Malteser** · 04-05-2015, 08:50 PM

Thanks for your answer. The team names are in a different column (column A) in the same row, e.g. Arsenal is at A117 and their goals are at H117.

**FDibbins** · 04-05-2015, 08:54 PM

adjust the ranges as needed
=VLOOKUP(A6,$A$2:$H$500,8,0)

**Malteser** · 04-05-2015, 09:05 PM

Yes, thanks FDibbins, that makes everything much simpler! I was tediously writing out 20 or 30 IF statements before thanks to my lack of knowledge.

**Tony Valko** · 04-06-2015, 03:17 PM

Originally Posted by FDibbins

Maybe Tony was correct after all....

Of course he was!

**FDibbins** · 04-06-2015, 03:18 PM

Originally Posted by Tony Valko

Of course he was!

Whatever was I thinking? :p

Using large amounts of IF statements

LinkBack

Thread Tools

Rate This Thread

Display

Using large amounts of IF statements

Re: Using large amounts of IF statements

Re: Using large amounts of IF statements

Re: Using large amounts of IF statements

Re: Using large amounts of IF statements

Re: Using large amounts of IF statements

Re: Using large amounts of IF statements

Re: Using large amounts of IF statements

Re: Using large amounts of IF statements

Re: Using large amounts of IF statements

Re: Using large amounts of IF statements

Thread Information

Users Browsing this Thread

Similar Threads

Large amounts of data

Grouping large amounts of data

[SOLVED] Rearranging large amounts of data

Large Amounts of Data

Doing Analysis from large amounts of DATA

Bookmarks

Bookmarks

Posting Permissions