Custom Equations built off Index Match

[cdon61]

Hi,

By all means I am no VBA expert. But I have been tasked with trying to figure this out. My goal here is to make custom equations like =unit_count() that will return the unit count based off of the two letter code contained in the referenced cell. The attached contains the two letter codes and in subsequent column headers, the values I seek to create custom equations for.

Can anyone start me off in the right direction?


Thanks
-C

[cubangt]

Based on the few samples, would Index/Match work for you?

try this

Please Login or Register  to view this content.

[cdon61]

Unfortunately no. That wouldn't work. I'm trying to write a UDF that would allow me to input =property_name(A1) and have it return the full property name based off of the p-code inputted into A1. The idea is to have these UDFs be usable on any spreadsheet on my (or anothers) computer.

Appreciate the response though!

[cubangt]

I didnt realize you needed a UDF, so here you go

Please Login or Register  to view this content.

You can use it like this in the sheet

Please Login or Register  to view this content.

[cdon61]

hey! Much appreciated. But still not what I'm going for here. Hoping to get something that just requires one input.

I just want an equation that'll run an index match on this original spreadsheet and return a value on whatever spreadsheet I put the equation on. So like, the test toolbox sheet tells you that the unit count for AG is 200. Assuming I get a spreadsheet from a 3rd party that does not include that information but does include the p-code AG, I want to be able to insert an =unitcount() on that cell and have it return me the unit count on that 3rd party provided spreadsheet.

I need to study more :I

[cubangt]

If you only want it to expect one value, will the ranges for the lookup and return array always be the same?

If so then you can remove those parameters and hard code them in the function, so that the index/match still gets what i needs to work, but the user is only providing the lookup value.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[jindon]

cdon61,
This will return the matched value where;
L4 = Header name that has search value.
L5 = Search value.
M4 = Header name that has return value.

So if you change "address_property" to "unit_count" in M4, you will get 200.
Use in cell like

=IndexIf(A1:I4,L4,L5,M4)

Please Login or Register  to view this content.

[kvsrinivasamurthy]

This formula works. In N5

=IFERROR(INDEX($C:$C,AGGREGATE(15,6,ROW($C$2:$C$4)/(($A$2:$A$4=UPPER(L5))*($D$2:$D$4=M5)),1)),"")