Returning a value if an adjacent cell matches text in a list

[Schmett]

Hey guys, I posted a similar question here before, but I'm having another issue with the same problem.

What I'm trying to do is get a cell to return a value (in C) if the name in an adjacent cell (in B) comes up in a list (B34:B1000). It looks a little like this at the moment:

[TABLE="width: 500"]
<tbody>[TR]
[TD]B[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]Test 1[/TD]
[TD]=IFERROR(INDEX(D34:D1000,MATCH($B7,$B34:$B1000),0),0)[/TD]
[/TR]
[TR]
[TD]Test 2[/TD]
[TD]=IFERROR(INDEX(D34:D1000,MATCH($B8,$B34:$B1000),0),0)[/TD]
[/TR]
[TR]
[TD]Test 3[/TD]
[TD]=IFERROR(INDEX(D34:D1000,MATCH($B9,$B34:$B1000),0),0)[/TD]
[/TR]
[TR]
[TD]Test 4[/TD]
[TD]=IFERROR(INDEX(D34:D1000,MATCH($B10,$B34:$B1000),0),0)[/TD]
[/TR]
[TR]
[TD]Test 5[/TD]
[TD]=IFERROR(INDEX(D34:D1000,MATCH($B11,$B34:$B1000),0),0)[/TD]
[/TR]
</tbody>[/TABLE]

The thing I really don't understand is that it works fine for the first two rows, then doesn't work thereafter. I've checked and double checked the spellings of names in the list, and that's not the case.

Any ideas as to why it's not working?

Thanks a bunch.

 

[Weazel]

you probably need to lock the lookup array so its not moving as you drag the formula down

=IFERROR(INDEX($D$34:$D$1000,MATCH($B7,$B$34:$B$1000),0),0)

 

[Schmett]

you probably need to lock the lookup array so its not moving as you drag the formula down

=IFERROR(INDEX($D$34:$D$1000,MATCH($B7,$B$34:$B$1000),0),0)

Thanks for the tip, but hasn't solved the problem. It's still just displaying "0" in every cell except for the first two.

 

[Weazel]

can you post a few rows of the data ?

maybe try modifying the match for an exact match ?

=IFERROR(INDEX($D$34:$D$1000,MATCH($B7,$B$34:$B$1000,0)),0)

 

[Schmett]

can you post a few rows of the data ?

Sure. I'll post it exactly how it is in my Excel spreadsheet. Here's where I'm trying to look up data:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]Aldi[/TD]
[TD]=IFERROR(INDEX($D$34:$D$1000,MATCH($B7,$B$34:$B$1000),0),0)[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]Asda[/TD]
[TD]=IFERROR(INDEX($D$34:$D$1000,MATCH($B8,$B$34:$B$1000),0),0)[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]CO-OP[/TD]
[TD]=IFERROR(INDEX($D$34:$D$1000,MATCH($B9,$B$34:$B$1000),0),0)[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]Lidl[/TD]
[TD]=IFERROR(INDEX($D$34:$D$1000,MATCH($B10,$B$34:$B$1000),0),0)[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]M&S[/TD]
[TD]=IFERROR(INDEX($D$34:$D$1000,MATCH($B11,$B$34:$B$1000),0),0)[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]Morrisons[/TD]
[TD]=IFERROR(INDEX($D$34:$D$1000,MATCH($B12,$B$34:$B$1000),0),0)[/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD]Sainsbury[/TD]
[TD]=IFERROR(INDEX($D$34:$D$1000,MATCH($B13,$B$34:$B$1000),0),0)[/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD]Tesco[/TD]
[TD]=IFERROR(INDEX($D$34:$D$1000,MATCH($B14,$B$34:$B$1000),0),0)[/TD]
[/TR]
[TR]
[TD]15
[/TD]
[TD]Waitrose[/TD]
[TD]=IFERROR(INDEX($D$34:$D$1000,MATCH($B15,$B$34:$B$1000),0),0)[/TD]
[/TR]
</tbody>[/TABLE]

And this is where it's getting it from:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[/TR]
[TR]
[TD]34
[/TD]
[TD]Aldi[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]35
[/TD]
[TD]Asda[/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]36
[/TD]
[TD]CO-OP[/TD]
[TD][/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]37
[/TD]
[TD]Lidl[/TD]
[TD][/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]38
[/TD]
[TD]M&S[/TD]
[TD][/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]39
[/TD]
[TD]Morrisons[/TD]
[TD][/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]40
[/TD]
[TD]Sainsbury[/TD]
[TD][/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]41
[/TD]
[TD]Tesco[/TD]
[TD][/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]42
[/TD]
[TD]Waitrose[/TD]
[TD][/TD]
[TD]9[/TD]
[/TR]
</tbody>[/TABLE]

And the table extends down all the way to row 1000.

 

[Schmett]

can you post a few rows of the data ?

maybe try modifying the match for an exact match ?

=IFERROR(INDEX($D$34:$D$1000,MATCH($B7,$B$34:$B$1000,0)),0)

Hey, that seemed to work! I'm not great with Excel though...can you tell me exactly what you did that made it work suddenly?

Thanks for the help.

 

[Weazel]

you can try either one of these formulas.....

I think the sumif might be a little more efficient for 1000 rows, though I didn't time it

Excel 2012

	A	B	C	D	E
Aldi
Asda
CO-OP
Lidl
M&S
Morrisons
Sainsbury
Tesco
Waitrose

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]

[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]

[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]

[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]

[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]

[TD="align: right"]9[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]26[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]27[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]28[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]29[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]30[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]31[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]32[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]33[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]34[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]35[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Aldi[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]36[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Asda[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]37[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]CO-OP[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]38[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Lidl[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]4[/TD]
[TD="align: right"][/TD]

[TD="align: center"]39[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]M&S[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]40[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Morrisons[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]6[/TD]
[TD="align: right"][/TD]

[TD="align: center"]41[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Sainsbury[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]7[/TD]
[TD="align: right"][/TD]

[TD="align: center"]42[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Tesco[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]8[/TD]
[TD="align: right"][/TD]

[TD="align: center"]43[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Waitrose[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]9[/TD]
[TD="align: right"][/TD]

[TD="align: center"]44[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet7​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C7[/TH]
[TD="align: left"]=IFERROR(INDEX($D$35:$D$43,MATCH(B7,$B$35:$B$43,0)),0)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D7[/TH]
[TD="align: left"]=SUMIF($B$35:$B$43,B7,$D$35)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Weazel]

all we did was tell the match to do an exact match instead of an approximate match.
though looking at the data you posted everything appears to be sorted ascending so the approximate match should work ok, though it would require you to ensure the values you are looking up remain sorted.

If everything is going to be sorted you could probably go with something like

=LOOKUP(B7,$B$35:$D$43)

though I think I prefer the sumif

 

[Schmett]

all we did was tell the match to do an exact match instead of an approximate match.
though looking at the data you posted everything appears to be sorted ascending so the approximate match should work ok, though it would require you to ensure the values you are looking up remain sorted.

If everything is going to be sorted you could probably go with something like

=LOOKUP(B7,$B$35:$D$43)

though I think I prefer the sumif

Ah, so that's what placing that 0 inside of the MATCH function signified.

I don't think I quite require those SUMIF or LOOKUP functions for what I intended. The list actually spans quite a lot of columns, and goes across by month for a few years. All I needed to do was find the corresponding values for some companies if it found a matching name in the full list.

It all works fine now though, thanks very much.