I have one data sheet with information in columns a-j

i have a separate report that i need to populate from the data sheet

i need a formula that will go look for "c *** te" in column b of the data

sheet and if found then look for "customer assistant" in column d and if

found then find "job seeker" in the same data sheet and if found return the

value listed under "job seeker" to the report

so i think it should be a combo of vlookup and if statements but i am not

familiar with if statements and not very good with formulas.

C *** te is in the data sheet along with customer assistant and job

seeker...customer assistant will be found in column D and job seeker may be

found potentially in columns E-I.

Example:

C *** te

High Volume Title Job Seeker Testing Evaluation

Sales Associates 3 6 2

Cusomer Assistant 9 7 1

I need to look for c *** te and if found then look in the rows below for

customer assistant and then look for job seeker then if found look one row

down and return the value found directly below job seeker and return that

number to my report in a separate worksheet.

this is all very new to me so i will need it broken down in simple terms

please.

Thank for your help...this discussion board is the best excel resource i

have ever found.

[snipped]

You were not quite consistent in describing what you want, but I think

what you are saying is you want the value under "Job Seeker", in the row

for "Cusomer Assistant" [sic], under the heading "C *** te".

Here is one way that uses a few "helper columns". Since you say you are

new to some of this I will describe each formula.

I'm guessing you might want to extend this to look for other cities,

other titles, so I worked that in too.

Put the following in K1:N1

C *** te Cusomer Assistant Job Seeker Value

In K2

=MATCH(K$1,$B:$B,0)

This looks for the city name in column B and tells which row has it.

In L2

=MATCH(L$1,OFFSET(INDIRECT("B"&K2),0,2,999),0)-1

This looks for the title (Cusomer Assistant) in column D, and tells how

many rows below the city name it appears.

In M2

=MATCH(M$1,OFFSET(INDIRECT("B"&K2),1,0,1,8),0)-1

This scans the row below the city name for the opportunity (Job Seeker)

and tells how many columns to the right of B is appears.

In N2

=OFFSET(INDIRECT("B"&K2),L2,M2)

Finally, the value you are looking for! It uses the coordinates we

determined in L2 and M2 to locate the correct value.

With that done, should you want to find a value for another city, title,

etc., just change the cells K1:M1.

Hope this helps!

Thanks for the response...i have a few questions.

Do i enter these formulas in the data sheet or the report i am trying to

populate?

Also, how do i get it to return the value into the correct cell on my report?

Thanks again for your help!

Do i enter these formulas in the data sheet or the report i am trying to

populate?

Also, how do i get it to return the value into the correct cell on my report?

Thanks again for your help!

Theoretically you can put the formulas anywhere you prefer, but the

references will need to be changed manually. The final value cell can go

anywhere, just make sure it points to the other three supporting

formulas. And kudos to you by the way for trying to plow through

this--these are not the easiest formulas to master (I'm still trying

myself!)

Formula #1

=MATCH(K$1,$B:$B,0)

^^^ ^^^^^

K$1 points to the "selector cell" where you enter a city name.

$B:$B points to the data sheet. If the formulas are in a different

worksheet this will look something like 'Data Sheet'!$B:$B. If you use

the formula wizard the correct sheet name will be filled in for you.

Formula #2

=MATCH(L$1,OFFSET(INDIRECT("B"&K2),0,2,999),0)-1

^^^ ^ ^^

L$1 points to the title selector, similar to above.

INDIRECT("B"&K2) -- Things get trickier with the INDIRECT formulas.

"B" refers to the left-most column in the data sheet, the "anchor

column" if you will. Again, if the data and formulas are in different

worksheets you again need to ensure the sheet reference is correct,

unfortunately the wizard will not help you with this one, so you need to

insert the correct reference yourself e.g.,

INDIRECT("'Data Sheet'!B"&K2)

K2 points to formula #1, easily enough.

The remaining formulas are similar to above. These are the spots you

will need to watch:

Formula #3

=MATCH(M$1,OFFSET(INDIRECT("B"&K2),1,0,1,8),0)-1

^^^ ^ ^^

Formula #4

=OFFSET(INDIRECT("B"&K2),L2,M2)

^ ^^ ^^ ^^

Thanks again for your help...you are correct in that these formulas are WAY

over my head...but i love this stuff and am determined to learn it. :) so i

REALLY appreciate your help and patience. the 1st formula is working.

however the remainder are not. Below is my version of formula 2:

=MATCH(B380,OFFSET(INDIRECT("[In Process Reqs.xls]Summary by City and

Step'!$B:$B"&BV380),0,2,999),0)-1

b380=title of position

in process reqs=my data sheet

bv380=value of my first formula

Any ideas? Thanks again for your help!

over my head...but i love this stuff and am determined to learn it. :) so i

REALLY appreciate your help and patience. the 1st formula is working.

however the remainder are not. Below is my version of formula 2:

=MATCH(B380,OFFSET(INDIRECT("[In Process Reqs.xls]Summary by City and

Step'!$B:$B"&BV380),0,2,999),0)-1

b380=title of position

in process reqs=my data sheet

bv380=value of my first formula

Any ideas? Thanks again for your help!

LOL I knew I should not have deleted the test worksheet I built for this

project!

So, on the fly, I think you could try this:

=MATCH(B380,OFFSET(INDIRECT("'[In Process Reqs.xls]Summary by City and

Step'!B"&BV380),0,2,999),0)-1

Two issues to note:

Missing tick mark ' in front of external workbook reference

Do not reference the whole column $B:$B in the INDIRECT function. If you

look at how we are using INDIRECT, we are building up a string that will

serve as a cell reference. E.g., if BV380 has the value 10, then

INDIRECT("'[In Process Reqs.xls]Summary by City and Step'!B"&BV380)

becomes

INDIRECT("'[In Process Reqs.xls]Summary by City and Step'!B10)

which boils down to

='[In Process Reqs.xls]Summary by City and Step'!B10

... just a simple cell reference! The beauty of it is we can change the

row (or column) by inserting other formulas and wrapping it with INDIRECT.

Whoops! I got off track here.

INDIRECT('[In Process Reqs.xls]Summary by City and Step'!B10)

which boils down to the range

'[In Process Reqs.xls]Summary by City and Step'!B10

... just a simple reference! The beauty of it is we can change the

row (or column) by inserting other formulas and wrapping it with INDIRECT.

Sorry for the confusion.

sorry...i'm sure you are sick of me, but i am still getting a #ref! error

so if the result of bv380 is 33 does that mean the formula in bw380 is

looking in line 33 of my data sheet?

would it help if i sent you my actual spreadsheet or would that be asking

too much or too cumbersome?

thanks again...

so if the result of bv380 is 33 does that mean the formula in bw380 is

looking in line 33 of my data sheet?

would it help if i sent you my actual spreadsheet or would that be asking

too much or too cumbersome?

thanks again...

OMG!OMG!OMG!OMG!OMG!OMG!OMG!OMG!OMG!OMG!OMG!OMG!OMG!OMG!

I DID IT!!!

Well with signficiant eversomuchappreciated help from you!

this is the most awesome thing ever!

I still can't believe it actually worked and that i was able to actually

troubleshoot a few things on my own...i present this to my boss tomorrow.

i cannot possibly tell you how much i appreciate your help with

this...honestly, i never thought any of it would ever work but it does and it

is beautiful!

thanks SO VERY MUCH again for your help! you are THE BEST!!!

I DID IT!!!

Well with signficiant eversomuchappreciated help from you!

this is the most awesome thing ever!

I still can't believe it actually worked and that i was able to actually

troubleshoot a few things on my own...i present this to my boss tomorrow.

i cannot possibly tell you how much i appreciate your help with

this...honestly, i never thought any of it would ever work but it does and it

is beautiful!

thanks SO VERY MUCH again for your help! you are THE BEST!!!

2. multiple ifs & ands indirect/offset...need help desperately pl

3. Multiple Nested IFs, ORs, Ands

4. multiple ifs & ands indirect/offset...need help desperately please

5. multiple ifs ands & indirect or offset?

6. Ifs and Ands - I'm confused!

7. IF's & AND's

8. two questions: and's inside if's and checking the return code of a program

9. Nesting IF's and AND's - a bit of help please...

10. IFs ANDs and ORs

11. New IFS interface (jfs.ifs, udf.ifs for example)

12. multiple ifs. multiple fields, multiple criteria

13. multiple ifs, mutliple fields, multiple criteria

14. IF(AND(val1=VLOOKUP( );val2>=VLOOKUP( );val2<=VLOOKUP( );VLOOKUP(

9 post • Page:**1** of **1**