Excel Tips index match

Hello,

I would like some help writing the following VBA Macro:

I have one Spreadsheet which has the following:

A B C
00001 Dog 30

And another which has:

A B C D E F G H I
Number Dog Dog Apple Apple Cat Cat Table Table
00001 30 6/15 1 6/20 15 6/20 32 6/25

Previously I had used index and match to get the values.

I would like the macro to take the value from spreadsheet 1, of 00001 then look for the corresponding row in sheet 2, I would like it to take the value of 30, and look across the row, once it matches the value of 30 go up to the column name (B2) and if the column name = the name in spreasheet to ignore that value (eg. if 30 = dog in speadsheet #2 ignore this value) I would like it to continue to look across the spreadsheet for the closest value to 30 and extract the value, the date next to the value and the column header.

Using the example above, the result would be

00001 Table 32 6/25

Table being 32 which is the closest value to 30. I would like this also to be absolute (as in closest value + or -).

Any Ideas on how I can make this work?

Thanks for the help!

Daniel

hi sorry to bother you i just had a question regarding excel and was wondering if you could help out.

I have a spreadsheet employee file with the following columns:
Transit (which is a unique number)
Title (which is a position title)
and many other columns pertaining to employees
Each transit will have many different people with many different position titles.

I have received another file that has about 900 transits. For each of those transits, I need to find all the employees who's title contains "Manager Customer Service" There may be more than one Manager Customer Service in a given transit, or there could be none in a given transit.

There doesn't seem to be a way for me to use INDEX and MATCH for this, because if I match transits, each transit has many different titles and it will only return my first match.

Any ideas what I can use to do this? thanks a lot.

In the MrExcel.com "Your One Stop for Excel Tips & Solutions" advice on "Excel VLOOKUP and Index & Match" example given to find the employee number appears incorrect.

When I try to replicate the example in Excel 2007 the MATCH function appears to only work if the list is first sorted into alphabetical order, in the same way as VLookup. Without first sorting the example shown does not produce the correct result for "Smith, Joe".

Hello everyone,

I am looking to do an Index match function but want the formula to pull values that contain certain words in another cell.

This issue all stems from different naming conventions throughout the company (e.g. GE Capital is a subsidiary of General Electric). Basically I am trying to "Map" all of these to their entity names.

Example where it does not work:

I am wanting to map "GOLDMAN SACHS" to "GOLDMAN SACHS & CO". For some reason this returns a #N/A...

Example where it does:

"PPG INDUSTRIES INC" returns "PPG INDUSTRIES, INC."

Here is my formula:

=INDEX('All TRX Swen'!O:O,MATCH('12-29 Mapping'!C199,'All TRX Swen'!M:M,0))

Thank you very much for your help. I truly appreciate it!

Jasper

Hello,
I have a list of part numbers in column F10:F2510 sheet named "Worksheet".
I want to use VB to INDEX/MATCH a sheet named "WIM" and Index columns "A:Z". Column "A" is the matching part number and pull the data in column "C" and have the value populate in column "E" in sheet named "WORKSHEET". I want to be able to do this for any given part numbers in the range of F10:F2510.

Anyones help would be greatly appreciated.
Thanks - Josh

Here is the problem.
I have a shipping spreadsheet with an address and 8 tracking numbers for each address all in one line. Each tracking number is in it's own column.

I want to be able to enter in a tracking number (the last 8 digits actually), and return the address. I cannot seem to get Index & Match to search through all 8 columns of tracking numbers.

Here is the code I'm working around.
=INDEX(PHASE3!A:A,MATCH("1zx8754v03"&C13,PHASE3!G:N,0))

I am one indexing one page "Phase3" outside of the page I want to enter and receive data.

Returning data in Column A from Phase3.
In reference to "1zx8754v03"&C13.
Searching in PHASE3!G:N
Exact Match.

It will work if I just search one column PHASE3!G:G, but I want to search G:N

Any help??

Hey Team,

I previously had help with the following formula which works great! It searches Sheet 3 for a specific value then returns with a value from Sheet 2 if found.

=INDEX('Sheet 1'!B$1:B$215,MATCH(1,('Sheet 1!A$1:A$215"")*ISNUMBER(SEARCH('Sheet 2'!A$1:A$215,'Sheet 3'!AU21)),0))

I'd like to edit it so if that value isn't found then instead of a return value of '#N/A' it gives a customised message....is this possible?

Also users of the spreadsheet input values in Sheet 3 which auto populates Sheet 1....when there are no values in Sheet 3 then Sheet 1 looks messy (N/A's, 0's etc), is there anyway to hide values if cells are blank in Sheet 3?

Any help is greatly appreciated. Thanks!

Hi all,

Once again i come here hoping that somebody has a lot more skill than i do!

I am not sure how to attach a sheet here, nor how to post what I see, so will try to describe my setup as best possible.

Basically i have a table that i want to be able to do a fully customized dynamic data grab from.
The data will always be changing, so I do not want to fix things or rely on the data always being in the same spot.
I am using the INDEX and MATCH formulas, but not sure if they are the best way to do it, but it works well except for one part ...
Here is the formula i am using:

=INDEX(A2:D10,MATCH(A15,A2:A10,0),MATCH(B14,A1:D1,0))


The test set-up i have is as follows, hope this makes sense:
(ignore the " as they just denote text in a cell)

B1 = "aaaa"
D1 = "bbb"

A2:A10 = the numbers down 1-9 (1,2,3,4,5,6,7,8,9)
B2:B10 = "apple", "orange", "banana", "kiwi", "grape", "melon", "plum", "peach", "cherry"

C4:C8 = the numbers down 1-5 (1,2,3,4,5)
D4:D8 = "kiwi", "grape", "melon", "plum", "peach"

B15 contains the formula =INDEX(A2:D10,MATCH(A15,A2:A10,0),MATCH(B14,A1:D1,0))

A15 will the the vertical value i am looking for (e.g. 3) and B14 is the horizontal value i am looking for (e.g. aaa)



So if i do this:
A15 = 5 and B14 = "aaa" ... the result is 'grape'
A15 = 1 and B14 = "aaa" ... the result is 'apple'

However, if i do this:
A15 = 1 and B14 = "bbb" ... the result is 0
A15 = 5 and B14 = "bbb" ... the result is 'grape'

The section of this formula that stops me from having complete control, is that the column reference is always matching from column A (A2:A10)
I want this to be dynamic.
I want to reference from the column offset to the left once that is being defined in B14 (i.e. either "aaa" or "bbb")
so if I have "aaa" selected, i want it to match my desired value from the range A2:A10
if i have "bbb" selected, i want it to match my desired value from range C2:C10

So if i do this:
A15 = 1 and B14 = "bbb" ... the result is 'kiwi'
A15 = 5 and B14 = "bbb" ... the result is 'peach'


does that make sense?
How do i modify the index formula to have a double dynamic column match reference?

Thank you so much for any time and help you may be able to provide.

Kindest regards,
KJ

I know you can use index in match to perform a "left lookup". I am just having trouble writing the formula. What I need is very basic.

Column A - Bob Smith
Column B - Pizza


I want the formula to lookup Pizza and return Bob Smith.


Thanks,
Jared

I have got 2 worksheets in my excel file. One is the ordering database which allows colleagues to enter the data every time when they are ordering stocks, e.g. stock ID number, quantity, Payroll number, etc.. and the ordering note (print it out and send it to warehouse) is the other worksheet. My boss asked me to link the two database together, does anyone knows how to do this by combining the match and index function. His expectation is automatically fill in the whole ordering note by just entering a reference number into a box. Anyone can help? Many thanks indeed^^