Vlookup on a table with Multiple Worksheet references

Gman51015

New Member
Joined
Jul 17, 2013
Messages
2
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?
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,685
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

New Member
Joined
Jul 17, 2013
Messages
2
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,400
Messages
5,528,518
Members
409,821
Latest member
decibelpilot

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top