Look up Table Based on Data in More Than One Column 2

[wzal]

Hi, Everyone,

I am writing an application to look up table and do some calculation. Which funtion in Execel or VBA is available to find the value of column C based on column A and B?

Column A Column B Column C
1 1 3
1 2 4
2 1 5
2 2 6

Thanks,

 

[onlyadrafter]

Hello,

One option is to combine (CONCATENATE) the cells together i.e.

COLUMN D COLUMN E
=A1&B1 3

You will also need to combine the two cells that have the original data.

Is this feasible?




----------------------------------
Hope this helps.
----------------------------------

maybe only a drafter
but the best user at this company!

 

[InspEngr]

Use the "DGET" function.

For everything below I'll use the "database" exactly as you've set it up in your post with "Column A" being in cell A1 and the number 6 being in cell C5.

Copy the headings (only) into another section of the spreadsheet (must be exact). You actually only need "Column A" and "Column B". For the example below, I put "Column A" into cell E1 and "Column B" into cell F1. In the cell under "Column A" (cell E2) put the first number and do like wise under "Column B" (cell F2).

The DGET syntax would then look like this:

=DGET(A1:C5,"Column C",E1:F2)

The above method can be used with numerous columns, not just two. Obviously, you can have the user input the numbers in another part of the spreadsheet and then reference those numbers in cells E2 and F2.

If all of the above is confusing, I can email you the spreadsheet I created to make sure it all worked (but need an email address).
-InspEngr

 

[Mutt]

Can do this :

{=SUM(IF($A$2:$A$5=2,IF($B$2:$B$5=2,$C$2:$C$5,0),0))}

Should return value of 6 as last cell value in C column for example. Curly brackets vital else just sums all of column C!