×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • 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.

Students Click Here

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

Look up Table Based on Data in More Than One Column

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,

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.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login



News


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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close