How to make Vlookup or return results that are not in the first column of the array?

*QUESTION SOLVED by Jonmo1*

Im trying to use Vlookup or Index/Match (or any other function) retrieve a value (in this example, 500) in an array, which is associated with a row label ("Supermarket") that is NOT in the left-most column ("Date", it is under "Category"). The array cannot be changed to make this row's column the first of the array.

The table looks like this:

Date / Category / Value

Day 1 / Pet Expenses / 123
Day 2 / Restaurant / 231
Day 3 / Supermarket / 500
Day 4 / Plants / 213

There are arrays for every month. Since the supermarket row changes every month but the value column doesn't, I only need to make the row lookup flexible to change every month. The issue is that it is not on the left-most column. Could you please let me know if you have any suggestions? Many thanks!

Originally Posted by c.mayer

Thanks for your fast reply. Unfortunately, as I mentioned, the objective is to not change the array (which covers A:C) because it is the object of many other formulas. Any ideas?

I don't understand how this formula changes the array? It makes no sense at all.

=VLOOKUP("Supermarket",B:C,2,FALSE)
Goes down column B to find Supermarket. When found, it returns the value two columns over, counting column B as #1, it returns ----> 500

If your data is not always in columns A - C, then you can use a MATCH to find the column with the header "Category"

Maybe this...

=VLOOKUP("Supermarket",INDEX(A:C,0,2):INDEX(A:C,0,3),2,0)

Although, along with the others, I don't understand why you can't just use:

=VLOOKUP("Supermarket",B:C,2,0)

What makes you think you can't use that? Have you tried it?

1. "... The thing is that I need the array to span from A:C ...". You must understand that you do not have an "array". You have a range of cells that span three columns (in this case, A-C).
2. Formulas do not rearrange worksheets.
3. To use VLookup, YOU specify which column to find the search value in; you specify how many columns to move to the right, counting search column as #1.
4. The left column of the VLookup range is NOT the left column of the worksheet. VLookup does not automatically look in column A just because it's the left edge of the worksheet.

For instance in the "range of cells" below

	I	J	K	L	M
1	ID NUMBER	FIRST NAME	LAST NAME	CITY	STATE
2	257	A	AA	X	1
3	474	B	SS	Y	2
4	596	C	DEF	W	3

To return the city based on the id you use:

Please Login or Register  to view this content.

from the same data range use

Please Login or Register  to view this content.

to return the state base on the last name
Notice that, in the first case the left column is "I" and in the second, it's "K".

Good deal. Thanks for the feedback!

If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.