Help with LOOKUPS

[silentpro]

Please help...

This is the best way I can think of writing this out.

IF column C has ID of 1234, & column D has a value of 4, give me the value
of column E.


Thanks...

 

[Iriemon]

=vlookup(c1,d1:e10,2,false)

 

[Iriemon]

Sorry misunderstood the question after re-readinig it.

 

[Iriemon]

=INDEX(C3:C5,MATCH(A10&B10,$A$3:$A$5&$B$3:$B$5,0))

You must commit the formula using
CTRL-SHIFT-ENTER since this is an array formula

(hit CTRL-SHIFT-ENTER rather than ENTER when done)


adjust for your cell references.

 

[Francis]

try this, assume that your data start at row 2

=IF(AND(C2=1234,D2=4),E2,"not match")

--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another

 

[silentpro]

I think this one will work.....now if I am looking in a different worksheet
for this info, where would I add that?
Let's say the worksheet is named "main"

 

[silentpro]

Maybe I should've done this to begin with, but here's my example

From worksheet MAIN

ALLEN, FRED ZAOP046145 COURSE Red COURSER12047
ALLEN, FRED ZAOP046145 COURSE Blue COURSEBL451815
ALLEN, FRED ZAOP046145 COURSE Yellow COURSEYEL014784754
ALLEN, FRED ZAOP046145 COURSE Green COURSEG654
ALLEN, FRED ZAOP046145 GREY Course GREY4578416

The above would be in one worksheet

In another worksheet, let's say fred is beginning on row A2
I would like to display the results as such..

NAME ID COURSE RED
COURSE BLUE COURSE YELLOW COURSE GREEN GREY
COURSE
ALLEN, FRED ZAOP046145 COURSER12047 COURSEBL451815
COURSEYEL014784754 COURSEG654 GREY4578416


thanks...

 

[Billy Liddel]

I think that as the data is presented you would be better with a Pivot table
in the Data Menu. If the list is likely to expand then create a dynamic range
name for the table in MAIN!.


Count of Course ID Course
Name ID COURSE Blue COURSE Green
ALLEN, FRED ZAOP046145 1 1
SMITH, JOHN IJOK046145 1

shows part of the Pivot Table, if the Blue course is being Attended then it
shows 1 else zero.

Name is in the Row Heading, Course in the Column Headings, Course ID in the
DAta.

Select the Names field in the PT and format the field so that no subtotals
are shown or you will have two rows for each student.

HTH
Peter
Name is in the

 

[silentpro]

Since a pivot table only show's 0's or 1's opposed to the course ID, I
elected to not go that route.
Each individual will have their own course ID#.

Thanks though.....

 

[Billy Liddel]

You could try placing the course IDs in the rows, the courses in the page
field and the names in the columns. This gives you the following:

Course (All)

Count of Name Name
Course ID ALLEN, FRED SMITH, JOHN Grand Total
COURSEBL451815 1 1 2
COURSEG654 1 1
COURSER12047 1 1 2
COURSEYEL014784754 1 1 2
GREY457841 1 1
Grand Total 5 3 8

The student ID can also be linked to the Columns field. To show the data in
the manner stated requires a macro. And as the original data shows
inconsistancies, COURSE yellow, GREY course it might be difficult to program
correctly.

Peter