Choosing a Variable col_index_num in VLOOKUP

[esohn92]

Hello Everyone,

I am new in this forum, and I have a problem that I can't seem to solve.

I am using a VLOOKUP function to pull data from a different excel, and I want to see if it's possible to use an equation that will determine the col_index_number.

So here is a simplified version of my problem:
excel problem.PNG

So I would like to use VLOOKUP to pull total value of Cutting Fruits (Cutting total cost of apple + banana + orange).

The problem here is that Cutting rate is not in the same column, and I would like to find a way for excel to look for the word "Cutting" for its specific fruit and extract the cutting value which is in the next column.

I've been pondering about this for a while, and I thought I would reach out to the community. Any help/comments is very appreciated!

Thanks,


Eugene

[Jonmo1]

Can you post an actual XL Book instead of a picture?
Some people are behind firewalls that don't allow those pics to go through.
Plus, it's just easier to work with a book, you can't copy the data from a picture into your own book to work with it..

[esohn92]

I dont think image is working. Here is what the excel sheet looks like:

Name Count Fee_Name_1 Fee_Rate_1 Fee_Value_1 Fee_Name_2 Fee_Rate_2 Fee_Value_2
Apple 5 Cutting $2.00 10 Seeding 1 $5.00
Banana 8 Peeling $3.00 24 Cutting 2 $16.00
Orange 10 Cutting $2.00 20 Peeling 1 $10.00



Peeling Total Cost
=VLOOKUP(A14,$A$1:$H$4,?????,FALSE)+VLOOKUP(B14,$A$1:$H$4,?????,FALSE)+VLOOKUP(C14,$A$1:$H$4,?????,FALSE)




Apple Orange Banana

[esohn92]

Sorry you're right Jonmo1. I don't know how to upload a file here but here is the goolge spreadsheet link:
https://docs.google.com/spreadsheets...gid=2016972759
Thank you!

Can you post an actual XL Book instead of a picture?
Some people are behind firewalls that don't allow those pics to go through.
Plus, it's just easier to work with a book, you can't copy the data from a picture into your own book to work with it..

[Jonmo1]

To upload a book..

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[esohn92]

done

Eugene

To upload a book..

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[oeldere]

I restructured your data.

After that an pivot table.

See the attached file.

[Jonmo1]

Wow..
Inconsistent data is the root of all evil in Excel.

There's nothing you can do about the source of this data being more organized? So each fee type is in the same column?

I'd make some helper columns to get those into some sort of order..
Then you can use standard vlookups with a known colindex#

Something like

EFesohn.xlsx

[esohn92]

hmmm, so you guys are saying there is no way to do this without editing/touching the data? my goal is to not touch the data at all and still pull up the right data. there has to be a way for this!

[oeldere]

I bet there is.

But do you want to work efficient?

In that case you are better off changing the data (imput).

So I strongly advice you to change the data in a more usefull style.

[Jonmo1]

Here is a very inefficient method (I still recommend a path toward better structured data)

Array formula entered with CTRL + SHIFT + ENTER
=SUM(IF(ISNUMBER(MATCH(A2:A4,A14:C14,0)),IF(C2:F4="Peeling",E2:H4)))