IF AND OR functions with VLOOKUP (2 conditions)

Hello, this is my first post.

I am an electrical engineer in the UK and in need of some help. I am trying to make the most of a quiet couple of days to set up some spreadsheets that will speed up the way we work.

I aim to set up a template spreadsheet (SS1) that will display data from a central spreadsheet (SS2) determined by 2 values entered in SS1.

A single fixed cell (F2) in SS1 will display 1 of 3 possible options A, B or C.

A column of data (C) in SS1 has 60 possible options AA, BB, CC and so on.

Depending on the combination of values that are true, a value will be displayed from a set of data in SS2.

The single letter will determine the column, and the pair of letters will determine the row the data is taken from in SS2.

I can work this for the single letter within column C in SS1 using;
=IF($C7<>"",VLOOKUP($C7, 'SS2'!$A$5:$I$72, 14, FALSE), "") Where 14 is the column that corresponds to the value in cell C7.

Adding the second bit of information (fixed cell displaying A, B or C) is proving quite difficult for me.

I would really appreciate some advice on this matter.

Thanks in advance.

Can you post a small sample Excel file.

To upload a file click "Go Advanced" than scroll down to "Manage Attachments".



I think you are looking for INDEX/MATCH

=INDEX(Data-range,MATCH(C7, SS1!C2:C61,0),MATCH(F2,SS1!colrng,0)

Data-range is the range where you have data to be "extracted"

C2:c61 contains AA,BB,CC etc

col_range are the columns for F2 value (A, B, C)

Thankyou for the swift response.

I understand that the function you have suggested will be more suitable so will have a play around with that. At the moment I am getting #name and #NA errors.

I have uploaded an example of the spreadsheet I am testing this on.

Thanks again.

We will need to know the "L" ("P", "M") values rather than the "A" ("B", "C") .

Try

=INDEX('SS2'!$C$5:$S$9,MATCH($C6,'SS2'!$B$5:$B$8,0),MATCH($F$2,'SS2'!$C$1:$T$1,0))

This returns the FIRST value of A, B or C: hence need to know which column 1 to 6.

If F2 contained L1, L2 etc the above will give desired result.

Thanks John, that makes sense.

Would it overcomplicate things, if, in the spreadsheet I sent you, I added the L1, L2, L3 titles to columns in SS1 to provide a 3rd variable, so the value extracted will reflect a value from SS2, for example, on row XX, in section X, under one column between L1-L6.

I have attached an example. I'm sure I'll get my head around this eventually!

Try

=IFERROR(INDEX('SS2'!$C$5:$S$9,MATCH($C6,'SS2'!$B$5:$B$8,0),MATCH($F$2,'SS2'!$C$1:$T$1,0)+(D$5-1)),"")

Row 5 from D to I contains number 1 to 6

See the attched

Thanks again John.

So you're suggesting replacing the L1,L2,L3 with basic numbers. What if the L2 is required to be a word? Is there a way of putting that into the equation, or would my only option be to pick a number to represent the word, and hide it in the cell above or below?

No you can have a word: this is the issue with examples that don't match your "real" world!!!!.

I used a number because you used L1, P1 etc so this being the case, a number would suffice (as we try to get a generic formula)..

Post a sample of the file with your real data headings.

In D6 and copy down

=INDEX('Central Load Data'!$C$6:$T$68,MATCH('Load Assessment'!$B6,'Central Load Data'!$B$6:$B$68,0),(MATCH('Load Assessment'!$A$1,'Central Load Data'!$C$2:$T$2,0)-1)+MATCH('Load Assessment'!$D$4,'Central Load Data'!$C$5:$H$5,0)+MATCH($D$3,'Central Load Data'!$C$4:$H$4,0)-1)

in H6 and copy down

=INDEX('Central Load Data'!$C$6:$T$68,MATCH('Load Assessment'!$B6,'Central Load Data'!$B$6:$B$68,0),(MATCH('Load Assessment'!$A$1,'Central Load Data'!$C$2:$T$2,0)-1)+MATCH('Load Assessment'!$D$4,'Central Load Data'!$C$5:$H$5,0)+MATCH($D$3,'Central Load Data'!$C$4:$H$4,0))

Similar formulas for POWER/MECHANICAL

Hi John

Thanks for your help. I now have a spreadsheet that functions as I desired, and I have learned something that will be quite useful.

Can you recommend any learning material I should look into purchasing, that may help us get the most out of excel in future?

The only book I have bought is on Excel VBA programming: my Excel knowledge has been acquired by "trial and error" PLUS a great deal from this forum (and still learning!).

If you search the net you will find a number of free courses on Excel which should get you started. I would recommend visiting this forum regularly to view some the questions, and more importantly, the answers.

It might be worth a visit to your "local" library to see if, and what, they have on offer.



For completeness, if your query has been addressed could you mark the thread as solved ("Thread Tools" at top of first post).