Vlookup on a table with Multiple Worksheet references

[Gman51015]

I have a question dealing with a variable column and cell reference I’m using. To make it simple here’s what I have done. On the FRUIT tab I have the following table in location BU132 – BV161
Name Column
Apples A
Pears B
Corn ‘VEGGIE’!A
Peas ‘VEGGIE’!B

I’m using the following formula to get the location of a cell on both the FRUIT and VEGGIE tab that has the value.
=INDIRECT(VLOOKUP(CH61,$BU$132:$BV$161,2,FALSE)&CN61) {where CH61-CH100 contains fruits or vegetables and where CN61 is a table that has the column reference which goes from 1 to 40)

This formula works great as long as the item is on the FRUIT tab. I can just drag the formula down the entire column and get my values for all the FRUITS listed in CH61-CH100. But, it doesn’t work for any items in CH which are on the VEGGIE Tab. What syntax do I need to use in the cell next to Corn and Peas above to get this to work right?

 

[sijpie]

It is not exactly clear what you are trying to do.

Please copy say cells BU132:BV136 (including some 'veggies') after putting borders on the cells, and then paste here in a post. That will give me a better idea.

 

[Gman51015]

What I was trying to do was much more complicated than it needed to be, so I simplified it plus put everything on one tab. It's not as automated as I desired but it's workable. I need to do a better job of setting up the data tables next time. Thanks for responding.