Multiple conditions using a table and IFS statement (or AND)

[janema]

Can someone tell me how to lookup off a table please? I am trying to create a formula to look up based off the below table. The columns would have to equal D & E to lookup column F.

The formula would go in column O below. I would want to say that if the values in column N match the data in the table column D and if the values in column M match the data in the table column E, then bring back column F from the table. Example: the data in that first cell in column O should bring back "Gabi Shute" because Column M is Isabel Bernal and Column N is Kevin Herde like on the table.

 

[etaf]

=INDEX($F$2:$F$1000,MATCH(N2&M2,$D$2:$D$1000&$E$2:$E$1000,0))

Book8
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1				Top Leader	leader	HRBP Name							leader	Top Leader	HRBP
2				NameA-1	NameB-1	Row2							NameB-5	NameA-5	Row6
3				NameA-2	NameB-2	Row3							NameB-6	NameA-6	Row7
4				NameA-3	NameB-3	Row4							NameB-7	NameA-7	Row8
5				NameA-4	NameB-4	Row5							NameB-8	NameA-8	Row9
6				NameA-5	NameB-5	Row6							NameB-9	NameA-9	Row10
7				NameA-6	NameB-6	Row7							NameB-10	NameA-10	Row11
8				NameA-7	NameB-7	Row8							NameB-11	NameA-11	Row12
9				NameA-8	NameB-8	Row9
10				NameA-9	NameB-9	Row10
11				NameA-10	NameB-10	Row11
12				NameA-11	NameB-11	Row12
13				NameA-12	NameB-12	Row13
14
Sheet1

Cell Formulas
Range	Formula
O2:O8	O2	=INDEX($F$2:$F$1000,MATCH(N2&M2,$D$2:$D$1000&$E$2:$E$1000,0))

Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

 

[janema]

=INDEX($F$2:$F$1000,MATCH(N2&M2,$D$2:$D$1000&$E$2:$E$1000,0))

Book8
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1				Top Leader	leader	HRBP Name							leader	Top Leader	HRBP
2				NameA-1	NameB-1	Row2							NameB-5	NameA-5	Row6
3				NameA-2	NameB-2	Row3							NameB-6	NameA-6	Row7
4				NameA-3	NameB-3	Row4							NameB-7	NameA-7	Row8
5				NameA-4	NameB-4	Row5							NameB-8	NameA-8	Row9
6				NameA-5	NameB-5	Row6							NameB-9	NameA-9	Row10
7				NameA-6	NameB-6	Row7							NameB-10	NameA-10	Row11
8				NameA-7	NameB-7	Row8							NameB-11	NameA-11	Row12
9				NameA-8	NameB-8	Row9
10				NameA-9	NameB-9	Row10
11				NameA-10	NameB-10	Row11
12				NameA-11	NameB-11	Row12
13				NameA-12	NameB-12	Row13
14
Sheet1

Cell Formulas
Range	Formula
O2:O8	O2	=INDEX($F$2:$F$1000,MATCH(N2&M2,$D$2:$D$1000&$E$2:$E$1000,0))

Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

It won't let me upload and excel sheet or download mini-sheet so unfortunately, I can't. I am trying to find the easiest way to get the data. One of our consultants said possibly using "IFS" would work? Or maybe "AND"?

 

[etaf]

if you click on the icon of 2 bits of paper 1 on top of the other - under the F(x) icon
then that will copy into the clip board on the PC

then goto excel and click in A1 and paste

i will add to a share dropbox - but will only be on a few days

Dropbox - File Deleted - Simplify your life

www.dropbox.com

One of our consultants said possibly using "IFS" would work? Or maybe "AND"?

OK, why ?
isn't the lookup working for you
Not sure how i would use IFS or AND to do this , hence why i posted the index/match

 

[janema]

if you click on the icon of 2 bits of paper 1 on top of the other - under the F(x) icon
then that will copy into the clip board on the PC

then goto excel and click in A1 and paste

i will add to a share dropbox - but will only be on a few days

Dropbox - File Deleted - Simplify your life

www.dropbox.com

OK, why ?
isn't the lookup working for you
Not sure how i would use IFS or AND to do this , hence why i posted the index/match

Didn't bring back the right answers, but maybe I typed it in wrong. I can try again.

 

[janema]

I tried this and got another error.

=INDEX(Status!D2:F37,MATCH(Status!D3&Status!E3,Status!F3:F37,0))

The Status! is the sheet where I'm looking up the table. I'm LOST.

 

[janema]

Still getting an error for this too. Don't think this is going to work. <sad face>

=INDEX(Status!$F$2:$F$37,MATCH(Census!M4&Census!N4,Status!$D$2:$D$37&Status!$E$2:$E$37,0))

 

[etaf]

but maybe I typed it in wrong.

yes

you have M4 and N4 the wrong way round

it would be useful to have an XL2BB copy or a share - as posted in my first post - makes things so much easier and quicker

you said

I would want to say that if the values in column N match the data in the table column D and if the values in column M match the data in the table column E,

so
=INDEX(Status!$F$2:$F$37,MATCH(Census!N4&Census!M4,Status!$D$2:$D$37&Status!$E$2:$E$37,0))

we are looking up the value in cell N4 and looking that up in Column D row 2 to 37
And also looking up the value in cell M4 and looking that up in Column E row 2 to 37

otherwise you are matching column M with D and N with E - which is why you get a #N/A error i suspect

the way the cells and columns are concatenated is important
we joint together N4 and M4 to make 1 trxt
and then look in joining D and E
so the N looks in D and the M looks in E
hence the order

The $ fix the cells - so as you copy down , they dont move
otherwise
Status!F2:F37
as you copy down will become
Status!F3:F38
Status!F4:F39
etc
so not looking up the entire rows - as you copy down and again will give an error

this is using the sheet names you mention

Dropbox - File Deleted - Simplify your life

www.dropbox.com

Lookup Names -ETAF2.xlsx
	M	N	O
1	leader	Top Leader
2
3
4	NameB-7	NameA-7	Row7
5	NameB-8	NameA-8	Row8
6	NameB-6	NameA-6	Row6
7	NameB-10	NameA-10	Row10
8	NameB-11	NameA-11	Row11
census

Cell Formulas
Range	Formula
O4:O8	O4	=INDEX(Status!$F$4:$F$24,MATCH(N4&M4,Status!$D$4:$D22&Status!$E$4:$E$24,0))

Lookup Names -ETAF2.xlsx
	D	E	F
1	Top Leader	leader	HRBP Name
2	NameA-1	NameB-1	Row1
3	NameA-2	NameB-2	Row2
4	NameA-3	NameB-3	Row3
5	NameA-4	NameB-4	Row4
6	NameA-5	NameB-5	Row5
7	NameA-6	NameB-6	Row6
8	NameA-7	NameB-7	Row7
9	NameA-8	NameB-8	Row8
10	NameA-9	NameB-9	Row9
11	NameA-10	NameB-10	Row10
12	NameA-11	NameB-11	Row11
13	NameA-12	NameB-12	Row12
Status

 

[janema]

yes

you have M4 and N4 the wrong way round

it would be useful to have an XL2BB copy or a share - as posted in my first post - makes things so much easier and quicker

you said


so
=INDEX(Status!$F$2:$F$37,MATCH(Census!N4&Census!M4,Status!$D$2:$D$37&Status!$E$2:$E$37,0))

we are looking up the value in cell N4 and looking that up in Column D row 2 to 37
And also looking up the value in cell M4 and looking that up in Column E row 2 to 37

otherwise you are matching column M with D and N with E - which is why you get a #N/A error i suspect

the way the cells and columns are concatenated is important
we joint together N4 and M4 to make 1 trxt
and then look in joining D and E
so the N looks in D and the M looks in E
hence the order

The $ fix the cells - so as you copy down , they dont move
otherwise
Status!F2:F37
as you copy down will become
Status!F3:F38
Status!F4:F39
etc
so not looking up the entire rows - as you copy down and again will give an error

this is using the sheet names you mention

Dropbox - File Deleted - Simplify your life

www.dropbox.com

Lookup Names -ETAF2.xlsx
	M	N	O
1	leader	Top Leader
2
3
4	NameB-7	NameA-7	Row7
5	NameB-8	NameA-8	Row8
6	NameB-6	NameA-6	Row6
7	NameB-10	NameA-10	Row10
8	NameB-11	NameA-11	Row11
census

Cell Formulas
Range	Formula
O4:O8	O4	=INDEX(Status!$F$4:$F$24,MATCH(N4&M4,Status!$D$4:$D22&Status!$E$4:$E$24,0))

Lookup Names -ETAF2.xlsx
	D	E	F
1	Top Leader	leader	HRBP Name
2	NameA-1	NameB-1	Row1
3	NameA-2	NameB-2	Row2
4	NameA-3	NameB-3	Row3
5	NameA-4	NameB-4	Row4
6	NameA-5	NameB-5	Row5
7	NameA-6	NameB-6	Row6
8	NameA-7	NameB-7	Row7
9	NameA-8	NameB-8	Row8
10	NameA-9	NameB-9	Row9
11	NameA-10	NameB-10	Row10
12	NameA-11	NameB-11	Row11
13	NameA-12	NameB-12	Row12
Status

I will try this again now, thanks. I know you said it would be useful to have an XL2BB copy or a share, but I tried several times and it just won't work for me unfortunately. I think my computer blocks it (work computer).

 

[janema]

It worked after I switched M4 and N4! Thank you soooooo much!!!!