CODEWITHSUNDEEP
excelexcel-formula
jtsmith1287
jtsmith1287

Reputation: 1149

Excel - Mutiple IFs in one cell

Alright, the title is a hair misleading. First, I'm in Google Spreadsheet. Second, This more than a IF(condition, return IF(...)) kind of situation (Also, I was looking around to see if I could even post an excel formula question on this site... didn't see much so if this is not the proper place feel free to say so and I'll delete).

I have a table that I need to get a value from based on 2 cells:

   -3 -2 -1 0  1  2  3  4  5
  ___________________________
A| 0, 0, 0, 1, 2, 4, 8, 12,16
B| 0, 0, 1, 2, 4, 8, 12,16,20
C| 0, 1, 2, 4, 8, 12,16,20,24
D| 1, 2, 4, 8, 12,16,20,24,28

I basically need C1 to equal what would be returned if A1 was "A", "B", "C", or "D" , and B1 was in the range of -3 - 5.

All of my attempts have been #error, so I don't have anything to show for the last hour I've put into figuring this out. I'm confident there's a way though. If you want my failed attempts let me know.

Upvotes: 0

Views: 742

Answers (3)

ebsddd
ebsddd

Reputation: 1026

You can use VLOOKUP. For example, if you place the table at A5, make the following the formula for C1:

=VLOOKUP(A1,A5:J9,B1+5,FALSE)

(A5:J9 is your lookup table; you can move it wherever, even on another sheet)

Upvotes: 0

chris neilsen
chris neilsen

Reputation: 53126

Try using INDEX in array mode:

Assuming
data is in $G$9:$O$12
row labels (A..D) is in $F$9:$F$12
column labels in $G$8:$O$8

=IFERROR(INDEX($G$9:$O$12,MATCH($A$1,$F$9:$F$12,0),MATCH($B$1,$G$8:$O$8,0)),0)

This has the advantage that it does not rely on the column headers being integers in ascending order.

Upvotes: 2

Reinier Torenbeek
Reinier Torenbeek

Reputation: 17383

Suppose that your table is stored on the top left of Sheet2, then you can use the following formula in cell C1:

=IFERROR(VLOOKUP($A$1,Sheet2!$A$1:$J$5,$B$1+5,FALSE),0)

The VLOOKUP function looks up the row where your desired letter is (as given in A1). If it is found, it will return the value in the column indicated by the contents of B1 with 5 added to transpose to the right value. The FALSE parameter indicates that the lookup should do an exact match.

If the value is not found, the IFERROR wrapped around VLOOKUP will take care of inserting the 0.

I shared a Google Spreadsheet example, with the formulas on Sheet1 and the table on Sheet2.

Upvotes: 1

Related Questions