×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Are you an
Engineering professional?
Join Eng-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Look up Table Based on Data in More Than One Column2

Look up Table Based on Data in More Than One Column

(OP)
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,

Replies continue below

RE: Look up Table Based on Data in More Than One Column

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!

RE: Look up Table Based on Data in More Than One Column

2
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

RE: Look up Table Based on Data in More Than One Column

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!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!