VLookup & Match Formula yields N/A or wrong result- Unable to fix

[clhmms]

Hello most gracious gurus,

I have tried for about 2+ hours to fix my N/A error. This is what I have done:

• Changed all formats to general (except the item prices)
• Trimmed all cells
• Changed the text row/column headers to numbers
• Tried variations of the formula e.g. ending the formula with +1, false, and many other variations

Some of my attempts have yielded a price but never the correct price. I included a sample table for reference.

Does anyone have an idea? Any assistance would be greatly appreciated.
Thank you in advance for your time
Cassandra

PS- I am using Excel 2007


Below is a table of sample data. I didn't find where I could upload my xls.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]List$: Grp 1[/TD]
[TD]List$: Grp 2
[/TD]
[TD]Base $: Grp 1
[/TD]
[TD]Base $: Grp 2
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]ddd
[/TD]
[TD]aaa
[/TD]
[TD]ddd
[/TD]
[TD]ppp
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]sss
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CT4-3/8
[/TD]
[TD][TABLE="width: 197"]
<colgroup><col></colgroup><tbody>[TR]
[TD]W: 42 1/2" To 48", H: 57 3/4"
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]115
[/TD]
[TD]215
[/TD]
[TD]615
[/TD]
[TD]715
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CT4.5-3/8
[/TD]
[TD][TABLE="width: 197"]
<colgroup><col width="197"></colgroup><tbody>[TR]
[TD="width: 197"]W: 48" to 53 1/2", H: 57 3/4"[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]135
[/TD]
[TD]235
[/TD]
[TD]635
[/TD]
[TD]735
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CT5-3/8
[/TD]
[TD][TABLE="width: 197"]
<colgroup><col width="197"></colgroup><tbody>[TR]
[TD="width: 197"]W: 54" to 59 1/2", H: 57 3/4"[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]155
[/TD]
[TD]255
[/TD]
[TD]655
[/TD]
[TD]755
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 127"]
<colgroup><col width="127"></colgroup><tbody>[TR]
[TD="class: xl65, width: 127"]Select Model[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]CT4-3/8
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 127"]
<colgroup><col width="127"></colgroup><tbody>[TR]
[TD="class: xl65, width: 127"]Select Base Group[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Base $: Grp 1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Result Price
[/TD]
[TD]=VLOOKUP(C12,B8:G10,MATCH(C13,D3:G3),1)
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Vidar]

Hi Cassandra

I took an educated guess on your row and column numbers on this one. Try it out:
=INDEX(D8:G10,MATCH(C13,B8:B10,0),MATCH(C14,D3:G3,0))

D8:D10 is your price range
C13 is the model lookup value, B8:B10 is model lookup range
C14 is the Base Grup lookup value, D3:G3 is the List/Base lookup range

Vidar

 

[clhmms]

Greetings Vidar,

Thank you for taking the time to respond to my post. I used your formula and it worked . Each time someone helps me with a post I do something nice for someone to pass along the kindness.

Thank you again!

Hi Cassandra

I took an educated guess on your row and column numbers on this one. Try it out:
=INDEX(D8:G10,MATCH(C13,B8:B10,0),MATCH(C14,D3:G3,0))

D8:D10 is your price range
C13 is the model lookup value, B8:B10 is model lookup range
C14 is the Base Grup lookup value, D3:G3 is the List/Base lookup range

Vidar

 

[Vidar]

You're most welcome Cassandra! So nice of you to doing something kind to others!

Vidar