Vlookup / if / etc help :(

[STEVE24]

HELLO GUYS, COMPLETE BOG STANDARD EXCEL INTERMEDIATE HERE!

I HAVE AN ISSUE WITH A PROJECT IM WORKING ON, IF ANYONE HAS A SOLUTION PLEASE LET ME KNOW

OK I HAVE A DOCUMENT WITH MANY SHEETS, I WOULD LIKE A SHEET TO MATCH TWO CRITERIA OF ITS OWN CONTENT, AGAINST ANOTHER SHEET THAT CONTAINS COLUMNS CONTAINING DATA TO CHECK AGAINST...

ONCE IT HAS FOUND A LINE WITH BOTH THESE CRITERIA MATCHING, IT WILL NEED TO PULL A VALUE OUT THAT IS SITTING A FEW COLUMNS AWAY ON THIS ROW

IVE TRIED VLOOKUP AND SOME IFS BUT IM GETTING NOWHERE!

EXAMPLE BELOW:

JOB	TYPE	PRICE
A	X	(FORMULA HERE)
	Y	(FORMULA HERE)
	Z	(FORMULA HERE)
B	X	(FORMULA HERE)

<TBODY>
</TBODY>

...
THIS IS TO TAKE A VALUE ASSOCIATING TO THE NEXT SHEET:

JOB	TYPE	PRICE
A	W	100
A	X	120
A	Y	50
A	Z	500

<TBODY>
</TBODY>

THIS SECOND TABLE WILL BE PAGES LONG WITH SEVERAL OF THE SAME JOB NAMES BUT EACH WITH A DIFFERENT TYPE

AM I MAKING SENSE :s

HOPEFULLY SOMEONE CAN COME TO MY RESCUE

THANKS

 

[RoryA]

Please do not type posts in capital letters - it is the internet equivalent of shouting!

 

[STEVE24]

didnt even realise, apologies, was too bothered about getting over my issue

 

[Andrew Poulsom]

Welcome to MrExcel.

It would be easier if you repeated the labels in column A, but try eg:

=INDEX(Sheet2!C:C,MATCH(1,INDEX((Sheet2!A:A=INDEX(A$2:A2,MATCH(REPT("z",99),A$2:A2)))*(Sheet2!B:B=B2),),FALSE))

Change the reference to Sheet2 to suit.

 

[STEVE24]

Also apologies for the poor exomple in the first instance...

Thanks Andrew, however i believe my example has put you onto the wrong path, let me rephrase the problem:

So one sheet (which is the sheet that all the other sheets will refer to) will contain all the different 'jobs' eg: named a,b and c
This same sheet will contain all the 'types' against each 'job' eg: named x, y and z

Meaning we have something along the lines of below (but on a much larger scale):

Job	Type	value
a	x	100
a	y	50
a	z	85
b	x	110

<TBODY>
</TBODY>

imagine this sheet contains all the combinations of jobs and types

so on each individual other sheet, it will have just one job, along with each type and their corresponding values

what i require is for the individual job sheets to pull the values from the big main one and allocate them against the correct type

again, apologies because i am really not that great with this kind of stuff, but if the previous answer was correct, virtually slap me through your computer screen. Otherwise let me know your thoughts after a bit more of an explaination (hopfeully)

 

[Andrew Poulsom]

My formula assumes that the sheet containing all the combinations of jobs and types is named Sheet2 and was intended to fill in the PRICE column in the first table you posted (FORMULA HERE). Did you try it in C2 copied down?

 

[steve25]

here is my version of your formula Andrew...

=INDEX(Sheet2!$X$11:$Y$1262,MATCH(1,INDEX((Sheet2!$B$11:$C$1262=INDEX(JEWRY!$B$4,MATCH(REPT("JW2460",10),JEWRY!$B$4)))*(Sheet2!$F$11:$G$1262=JEWRY!C4),),FALSE))

im getting a #n/a in my cell though, copy down also creates #n/a in the other cells

ive changed the name of my master sheet to 'Sheet2' to keep things easy at the moment

the sheet that is to refer to the master sheet 'Sheet2' is called 'JEWRY' hence the name in my version

Any clues as to why its not working for me?

 

[Andrew Poulsom]

Firstly, are STEVE24 and steve25 the same person? That contravenes rule 14 here:

http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

As for your fomula, in which columns on Sheet2 are job, type and price (you are using two columns for each)? And in which columns are they on sheet JEWRY? You won't need to reference that sheet if the formula is on it.

 

[steve25]

yes, for some reason the site logged me out and wouldnt let me back in, even after resetting password etc... strange but created a second account to continue communicating about my issue...

the job type and price are using double columns (not sure why, this master sheet is not mine!) - would this pose a problem?

i have now removed all JEWRY references and have come out with this:

=INDEX(Sheet2!$X$11:$Y$1262,MATCH(1,INDEX((Sheet2!$B$11:$C$1262=INDEX($B$4,MATCH(REPT("JW2460",10),$B$4)))*(Sheet2!$F$11:$G$1262=C4),),FALSE))

Still getting back a #n/a in my cell

Any other clues Andrew? Thanks for your help so far

 

[Andrew Poulsom]

Do you really mean two columns or are the cells just merged? Maybe try:

=INDEX(Sheet2!X:X,MATCH(1,INDEX((Sheet2!B:B=INDEX(B$4:B4,MATCH(REPT("z",99),B$4:B4)))*(Sheet2!F:F=C4),),FALSE))